mysql query and offset question

[SEOPTI]

Is there a chance not to perform 3 mysql selects, I have tried for hours but I can't make a single mysql select from this, as you can see the select statement is exactly the same, just the offset changes.

<?php
$firstSp = mysql_query("SELECT bizID,bizName FROM biz_cars WHERE bizState='$bizState' AND bizCity='$bizCity' AND bizLive='1' GROUP BY bizName LIMIT $offset,1");
$first = mysql_fetch_array($firstSp);
?>

<?php
$secondSp = mysql_query("SELECT bizID,bizName FROM biz_cars WHERE bizState='$bizState' AND bizCity='$bizCity' AND bizLive='1' GROUP BY bizName LIMIT $newoffset,1");
$second = mysql_fetch_array($secondSp);
?>

<?php
$betterdesc = mysql_query("SELECT bizID,bizName FROM biz_cars WHERE bizState='$bizState' AND bizCity='$bizCity' AND bizLive='1' GROUP BY bizName LIMIT $newdesc,1");
$betterdesc = mysql_fetch_array($betterdesc);
?>

[whoisgregg]

I think that the code below is what you are looking for... It's hard to tell for sure because I don't see how $offset, $newoffset, and $newdesc are being set. :/

$query = "SELECT bizID,bizName FROM biz_cars WHERE bizState='$bizState' AND bizCity='$bizCity' AND bizLive='1' GROUP BY bizName LIMIT $offset,3";
$result = mysql_query($query);
while ($row = mysql_fetch_assoc($result)) {
 $results[] = $row;
}
print_r($result[0]); //equivalent to your $first
print_r($result[1]); //equivalent to your $second
print_r($result[2]); //equivalent to your $bettersdesc

If that isn't what you were expecting, please post back with some more details about what you are trying to accomplish. :)

[SEOPTI]

Thank you for your help, it didn't work, sorry but I forgot to tell that the offsets are different, I need to find a new way to use 3 different offsets with one SELECT query.

These are the different offsets:

<?php

$letter = strtoupper($_GET['letter']);

$newoffset = $_GET["newoffset"];
if(!$newoffset)
{
$newoffset = $offset+30;
}
?>

<?php

$letter = strtoupper($_GET['letter']);

$newo = $_GET["newo"];
if(!$newo)
{
$newo = $offset+31;
}
?>

<?php

$letter = strtoupper($_GET['letter']);

$newdesc = $_GET["newdesc"];
if(!$newdesc)
{
$newdesc = $offset+1;
}

?>

[SEOPTI]

Please ignore my last message, I got it.

It is strange that print_r outputs the results, but not print and not echo. Print and Echo just output the word Array.

Is there a equivalent to print_r which will output just the array? I tried hundreds of possible combinations with echo and print, but it just shows the word Array.

[cameraman]

You would need to echo the array elements separately, for example:
foreach($result[0] as $value)
echo "$value<br>\n";

foreach($result[1] as $value)
echo "$value<br>\n";

foreach($result[2] as $value)
echo "$value<br>\n";

$result[0] is an array, of the fields specified in your query. $result[1] is another array, and $result[2] is a third.

[SEOPTI]

cameraman, thanks! This was the key.

[SEOPTI]

foreach works like a charm for arrays which are not empty, there is only one problem, if the array is empty I get a PHP Warning:
Invalid argument supplied for foreach()

I need those empty arrays because in some cases they are empty (no output) and in some cases they are not empty (output).

Is there a way to avoid this Warning and first check if the array is empty?

[edited by: SEOPTI at 3:31 pm (utc) on Sep. 22, 2007]

[SEOPTI]

Maybe this will do the check:
if (!empty($row)) { return false;}

but I'm not sure how to use this code together with foreach statement.

[cameraman]

Since the mysql_fetch functions return false if there's no data, you can use that:
if($result[0] === false) {
} // EndIf handle false
else {
foreach($result[0]) . . . . . .
} // EndElse have data

Notice that the if has three equal signs in it. When checking for a value of false, it's a good idea to get into the habit of doing it that way - the third = makes it check the type of value in addition to the value of the value. Without the type check, there's no difference between zero and false, whereas sometimes zero is significant (in this case it's not, but if you're in the habit you don't have to think about it each time).

[SEOPTI]

Thanks I will give it a try, I figured out, that this works also:

if(!empty($results[0])) {
foreach($results[0] as $value) { echo "$value";}}