Moving Through MySQL Entries

[drewprops]

I'm teaching myself a smidge of SQL.

My goal is to create a navigable web interface for design projects, information for which has been entered into a table in the database.

Let's say there are 10 projects; 5 are web design, 3 are illustration, 1 is graphic design, and 1 is copywriting.

I think I understand how to use a WHILE statement to whirl through the database to find and list projects that belong to a specific category (say "web design").

What I'd like to do is to immediately feature ONE project onscreen, with ALL its details displayed (as pulled from the table), and provide "next" and "previous" buttons which will call up other projects from the same category of "web design".

I know how to do this this from a table, because you use a key to move through the array.

Is there a similar technique that you use with PHP/SQL to move around? Pointers of some kind?

It's hard learning this stuff at such an old age, but I'm sure that I'll get it eventually.

Thanks to any of you peeps who might be able to explain this simply.

Because I'm simple.

:)

[drewprops]

So far I've learned to add an AUTO_INCREMENT column that will help me at some point, possibly...

And this discussion might help me "How can I get the nearest rows in mysql? (http://stackoverflow.com/questions/7184965/how-can-i-get-the-nearest-rows-in-mysql?lq=1)"




...

[drewprops]

I'm getting this working, slowly... using the Internet.

Learning new code techniques is painful for the visually oriented.



...

[adamb]

Not sure I understand what's happening here, but having an auto increment primary key is the way to go.

For simplicity I would do something like:

select top 1 id from projects
where category = 'Web Design'
and id > $currentId (php variable)
order by id

If that returns a result then that id could then be used for your 'Next' button (which I guess in turn is used in your call to fetch the data specific to that project). Does that help at all or have I got the wrong end of things entirely?

[drewprops]

Adam thanks,

You're right on target, though I'm building in the necessary additional logic that I had not originally considered, for instance checking to see if a category had already been selected (passed as a variable), or if an out of range element has been selected by error or malice. I've also gone back to build in flexibility on the PHP end to accommodate changes to the categories array.

Shortly after posting my last question I learned about and added an auto incremented column and will soon figure out how to use that data to navigate around a set of isolated data in an ordered and linear (and wraparound) fashion.



...

[drewprops]

So my code is working, sort of.

It reaches in, searches through one particular column (named 'category') for the lowest value of the column named 'identNum'.

Like any n00b, I print out a lot of results to figure out what's happening.





// Run a query to find the lowest value of auto-incremented column 'identNum' from the table 'portfolio_database'
// where the category is isolated to the value in $thisCat

$query = mysqli_query($dbc, 'SELECT MIN(identNum) FROM portfolio_data WHERE (category="'.$thisCat.'")');

print_r ($query); // THIS IS A TEST, TO SEE WHAT'S INSIDE OF $query

while ($result = mysqli_fetch_array($query, MYSQLI_BOTH)){

// Print results
echo "<p>result[identNum] = ".$result['identNum']."</p>\n\r";
echo "<p>result[0] = ".$result[0]."</p>\n\r"; // THIS IS THE ONLY RESULT THAT PRINTS DATA
echo "<p>".$result[1]."</p>\n\r";
echo "<p>".$result[2]."</p>\n\r";
echo "<p>".$result[3]."</p>\n\r";
}





This results in printing:

Object ( [current_field] => 0 [field_count] => 1 [lengths] => [num_rows] => 1 [type] => 0 )

result[identNum] =

result[0] = 4

result[1] =

result[2] =

result[3] =




So, while $query is definitely returning an array, it seems to have only ONE thing in it, the value of the lowest value in the column identNum, from among the rows that have $thisCat in the 'category' column.

I now have to figure out if there is something in my original query I could change to return ALL of the data of the ENTIRE row associated with the qualifying identNum.



...

[adamb]

The reason for that is the SQL...

SELECT MIN(identNum) FROM portfolio_data WHERE (category="'.$thisCat.'")

...is saying "get me the lowest identNum field back with the specified category".

You could do the following to get all fields, though I'm not sure about how it maps to variables in php (it's been a while, I'll try and dig out some old stuff I did but my php is worse than my SQL...):

SELECT * from portfolio_data
WHERE identNum = (
SELECT MIN(identNum) FROM portfolio_data
WHERE (category="'.$thisCat.'")
)

That will return all fields in portfolio_data for the row with the min identNum value.

[drewprops]

Adam THANK YOU!!!

The logic of queries had me stymied and your explanation helped ENORMOUSLY!!

I had spoken to a friend at lunch who happens to work in SQL, and he said that he'd send me something later, but then I saw that you'd posted and that it made so much sense.

Now I'm off wrestling with the logic of multidimensional arrays.

It usually takes a day of poring over stuff before it seeps into my brain enough for it to 'click' in my mind.

I think I may go to be early and let the gears whirr all night long.

Thanks again! :)



...