Page 1 of 1

Use of Return in MySQL rows

Posted: Mon Dec 08, 2008 7:57 pm
by socket1
Hello all, I am new here and would like to pose a problem I have having.

Here goes:

I have a functions file with this function in it, the function mysqlConnect() is above and works fine.

Code: Select all

 
function categories() {
    global $MySQL;
    mysqlConnect();
 
    $query = "SELECT * FROM ".$MySQL['table_prefix']."".$MySQL['categories_table']."";
    $result = mysql_query($query) or die (mysql_error());
    
    while($row = mysql_fetch_array( $result )) {
    return "<option value=\"".$row['id']."\">".$row['category']."</option>";
    }
}
 

I then have a file that calls this function in a variable ex)

Code: Select all

$content = "Categories: <br /> <select name=\"category\">".categories()."</select>
 
What I am basically wanting to do is take all the rows from a MySQL table and turn it into a selectable list,
The problem is that when I do this, only the first row shows up in the select, because I realize return quits after it returns the first select, I was attempting to do this with arrays or echo, which would just echo the select at the top of the page before echo $content; was stated.

I do not have much experience with using arrays so any help would be useful !

Thanks a lot.

Re: Use of Return in MySQL rows

Posted: Mon Dec 08, 2008 8:02 pm
by SteveC
socket1 wrote:function categories() {
global $MySQL;
mysqlConnect();

$query = "SELECT * FROM ".$MySQL['table_prefix']."".$MySQL['categories_table']."";
$result = mysql_query($query) or die (mysql_error());

while($row = mysql_fetch_array( $result )) {
return "<option value=\"".$row['id']."\">".$row['category']."</option>";
}
}

Code: Select all

function categories() {
    global $MySQL;
    mysqlConnect();
 
    $query = "SELECT * FROM ".$MySQL['table_prefix']."".$MySQL['categories_table']."";
    $result = mysql_query($query) or die (mysql_error());
    
    $text="";
    while($row = mysql_fetch_array( $result )) {
        $text.= "<option value=\"".$row['id']."\">".$row['category']."</option>";
    }
    return $text;
}
I think that'll solve your problem.

A tip - if it's quite a large table, specifying the fields you want to use would speed the process up, and using mysql_fetch_row is usually quicker too. Example:

Code: Select all

function categories() {
    global $MySQL;
    mysqlConnect();
 
    $query = "SELECT id,category FROM ".$MySQL['table_prefix']."".$MySQL['categories_table']."";
    $result = mysql_query($query) or die (mysql_error());
    
    $text="";
    while($row = mysql_fetch_array( $result )) {
        $text.= "<option value=\"".$row[0]."\">".$row[1]."</option>";
    }
    return $text;
}

:)

Re: Use of Return in MySQL rows

Posted: Mon Dec 08, 2008 8:04 pm
by socket1
You're a genius! Thank you so much.

Re: Use of Return in MySQL rows

Posted: Mon Dec 08, 2008 8:19 pm
by SteveC
socket1 wrote:You're a genius! Thank you so much.
You're welcome :D

By the way, I have another tip.

If you're running a MySQL query like you are in that code, and you won't be running any other MySQL queries before you've dealt with all the data from that query, using mysql_unbuffered_query allows your script to start processing as soon as it receives the first row. Particularly useful when MySQL is running slowly, as your PHP can stay one step ahead.

Re: Use of Return in MySQL rows

Posted: Tue Dec 09, 2008 2:02 pm
by pickle
It's mostly a personal preference, but the less you use double quotes and escaping, the faster the script will parse:

Code: Select all

$text.= '<option value="'.$row[0].'">'.$row[1].'</option>';
There's no need to use double quotes - especially if the string you're quoting has double quotes anyway - just use single quotes.

Re: Use of Return in MySQL rows

Posted: Tue Dec 09, 2008 3:45 pm
by socket1
Thanks for both the tips! I will incorporate them into my system when I can.
For future reference... the $var.= just adds the current text onto the existing variable am I correct?

Nice sig pickle, haha.

Re: Use of Return in MySQL rows

Posted: Tue Dec 09, 2008 3:55 pm
by pickle
Ya, .= is the concatenate operator.

I'm assuming your mysqlConnect() function creates a connection to your database? I suggest you() make one connection, store the connection in your global MySQL array, then use that connection when calling mysql_query(). That way, the database doesn't get bogged down by lots of connections, and your script will execute faster (though probably imperceptibly) because it doesn't have to continually re-establish a connection with the database.

I didn't invent my sig - not sure where I originally saw it though.

Re: Use of Return in MySQL rows

Posted: Wed Dec 10, 2008 1:00 am
by SteveC
pickle wrote:It's mostly a personal preference, but the less you use double quotes and escaping, the faster the script will parse:

Code: Select all

$text.= '<option value="'.$row[0].'">'.$row[1].'</option>';
There's no need to use double quotes - especially if the string you're quoting has double quotes anyway - just use single quotes.
I had read this somewhere too, somebody did a bunch of tests. Regardless, I like the single quotes because they look cleaner, and I often need to run variables by functions such as htmlspecialchars - you know? Mixing all those double quotes and single quotes looks gross.

Re: Use of Return in MySQL rows

Posted: Wed Dec 10, 2008 2:36 pm
by socket1
pickle wrote:It's mostly a personal preference, but the less you use double quotes and escaping, the faster the script will parse:

Code: Select all

$text.= '<option value="'.$row[0].'">'.$row[1].'</option>';
There's no need to use double quotes - especially if the string you're quoting has double quotes anyway - just use single quotes.
It makes sense and looks cleaner without escape characters everywhere.
pickle wrote: I'm assuming your mysqlConnect() function creates a connection to your database? I suggest you() make one connection, store the connection in your global MySQL array, then use that connection when calling mysql_query(). That way, the database doesn't get bogged down by lots of connections, and your script will execute faster (though probably imperceptibly) because it doesn't have to continually re-establish a connection with the database.

I didn't invent my sig - not sure where I originally saw it though.
I read somewhere that if I have mysql_connect called multiple times it will just reuse the old connection if its still open.