MySQL - Is there a better way to SELECT

Acrylic · 10-24-2007, 03:00 PM

I am fairly new to the whole PHP MySQL stuff, I can make it work but don't know all the optimizations and such. I know very little MySQL and figured there has got to be a better way of doing this...

Currently I am doing this, each query is specific to a 'type' and each result is put into a different table.

PHP Code:


			
$result1 = mysql_query("SELECT * FROM user_links WHERE type='0' AND user_id='".$g_id."' ORDER BY id");

$result2 = mysql_query("SELECT * FROM user_links WHERE type='1' AND user_id='".$g_id."' ORDER BY id");

$result3 = mysql_query("SELECT * FROM user_links WHERE type='2' AND user_id='".$g_id."' ORDER BY id");

$result4 = mysql_query("SELECT * FROM user_links WHERE type='3' AND user_id='".$g_id."' ORDER BY id");

$result5 = mysql_query("SELECT * FROM user_links WHERE type='4' AND user_id='".$g_id."' ORDER BY id");

$result6 = mysql_query("SELECT * FROM user_links WHERE user_id='".$g_id."'");

$num_rows6 = mysql_num_rows($result6);

so after thats done, each $result* is "echo'd" into a separate table to categorize them, $result6 and $num_rows6 is used to see if they even have any data in 'user_links' and if not then i prompt them with an add button. is there any better was instead of doing all those select statements and a better way to do what i descried with $result6?

Thanks!

Karl · 10-24-2007, 03:20 PM

To combine those first 5 queries you could do:

PHP Code:


			
$szQuery = sprintf('SELECT * FROM user_links WHERE type BETWEEN 0 AND 4 AND user_id = %d', $g_id);

$pResult = mysql_query($szQuery);

To get the count, there's two options, the first is to just mysql_num_rows the $pResult, but it depends if the user could have more user_links than that returned by the first query. I do imagine that there would be more than the 0-4 range, so here's another query to return the count.

PHP Code:


			
$szQuery = sprintf('SELECT * FROM user_links WHERE user_id = %d', $g_id);

$pCountResult = mysql_query($szQuery);

$iCount = mysql_num_rows($pCountResult);

Combining those two code snippets should give you the same result as what you're getting now.

On another point, here's a small article that you may find useful in regards to using sprintf for formatting query strings, Securing your MySQL Queries with Sprintf

Acrylic · 10-24-2007, 03:33 PM

Thanks Karl, but how would I separate each type (which only goto 4 btw) into their own table with only one select statement? Like I would have 5 tables each with a while loop for the $result*.

PHP Code:


			
while($row = mysql_fetch_array($result1, MYSQL_BOTH)){

OR am I just missing the picture and being a hard head? :)

Thanks though I will look into doing sprintf

Salathe · 10-24-2007, 03:39 PM

Use the mysql_fetch_array function to fetch all of the relevant rows into a structured array, which you can then output to your pleasure.

For Example

PHP Code:


			
$data = array();
while ($row = mysql_fetch_array(...)) {
    $data[$row['type'][] = $row;
}

Then you can loop through the array outputting different HTML tables as you go.