Select statement with arrays

[chazzy69]

Ok here is what im trying to achieve, i first retrieve from one part of my database names and email address, etc which i store in an array i.e.

while($row= mysql_fetch_array($result)){
$name[$i] = $row['name']
$suburb[$i] = $row['suburb']
}

this seems to work fine but when i try to use this information in a select statement for another part of my database it doesn't seem to like it, heres a example of what im tring to do - (note i renamed a couple of things),

Also for some reason bytes does not want to display the code so im gonna try re-writing it here (line 15)-

$sql = "SELECT var1, var2, var3 FROM table_name WHERE suburb="$suburb[$counter]" ;

for ($counter=0; $counter < $numtotal; $counter++){
$con = mysql_connect("localhost","user_name","pass");
        if (!$con)
          {
 
              die('Could not connect: ' . mysql_error());
          }
 
        else {
            echo "We have a connection" . "<br />";
            mysql_select_db("name_database", $con);
 
 
//this is where i seem to be getting the errors from            
$sql = "SELECT var1, var2, var3 FROM table_name WHERE suburb="$suburb['$counter']" ;
 
 
        $result = mysql_query($sql,$con);
        if (!$result)
          {
 
              die('Error: ' . mysql_error());
          }
 
$NUMlistings = 0;
        while($row = mysql_fetch_array($result))
          {
 
         $var1[$NUMlistings] = $row['var1'];
         $var2[$NUMlistings] = $row["var2"];
        $var3[$NUMlistings] = $row["var3"];
         $NUMlistings++;
        }
 
        mysql_close($con);
 
        }

So if anyone can give us a hint or perhaps point me in the right direction, thanks for anyhelp :)

[Atli]

Hi.

In your first code example, where is the $i variable coming from?
You are using a while loop, which doesn't increment anything automatically, and I don't see any code that would do it manually.

As it is, the $i variable is either set, and the while loop keeps overriding the same index of those arrays, or it isn't set and PHP evaluates it as 0 and overrides that index each iteration.

[chazzy69]

Sorry bout that, i must of missed it when copying it should look like

$i = 0;
while($row= mysql_fetch_array($result)){
 $name[$i] = $row['name']
 $suburb[$i] = $row['suburb']
 $i++;  
}

Anyway the line im having trouble with is -

$sql = "SELECT var1, var2, var3 FROM table_name WHERE suburb="$suburb[$counter]" ;

For some reason still can't put code tags around this without the tags deleting the line...

Anyway specifically can i use - WHERE suburb="$suburb[$counter]" ;
inside a loop.

Hope this clarifies my previous statement a bit

[chazzy69]

I believe i found the problem but im not entirely sure how to over come it.

First this works-

$state[0] = "nsw";
$state[1] = "qld";
$pcode[0] = 2000;
$pcode[1] = 3000;
$suburb[0]= "sydney";
$suburb[1]= "surfers paridise";
 
for ($i=0; $i < 2; $i++){
$con = mysql_connect("localhost","user_name","pass");
        if (!$con)
          {
 
              die('Could not connect: ' . mysql_error());
          }
 
        else {
            echo "We have a connection" . "<br />";
            mysql_select_db("name_table", $con);
 
 
 
            $sql = "SELECT var1, var2, var3 FROM latest_listings WHERE state='$state[$i]' AND suburb='$suburb[$i]'";
.
.
.
.

but this wont work -

$state[0] = "nsw";
$state[1] = "qld";
$pcode[0] = 2000;
$pcode[1] = 3000;
$suburb[0]= "sydney";
$suburb[1]= "surfers paridise";
 
for ($i=0; $i < 2; $i++){
$con = mysql_connect("localhost","user_name","pass");
        if (!$con)
          {
 
              die('Could not connect: ' . mysql_error());
          }
 
        else {
            echo "We have a connection" . "<br />";
            mysql_select_db("name_table", $con);
 
 
 
            $sql = "SELECT var1, var2, var3 FROM latest_listings WHERE state='$state[$i]' AND suburb='$suburb[$i]' AND pcode=pcode[$i]";
.
.
.
.

i know it wont work because of this line -

AND pcode=pcode[$i]

but pcode[$i] = to a integer, so i cant place quotation marks around like the other varibles. So any hints on how to overcome this would be greatly appreciated thanks,

[Atli]

@chazzy69
The trouble here is that you are messing up the quotes.
If you were to view this in an editor that can highlight PHP, it should become very obvious, as the code following that line would be highlighted as a string.

When you use double quotes, there is no need to close the quotes to insert a variable, you can just put it right in there and PHP will parse it. To make absolutely sure there is not problem when you do this, it's best to enclose the variable in brackets. Like:

$sql = "SELECT var1, var2, var3 
        FROM table_name 
        WHERE suburb={$suburb[$counter]}";

And if "suburb" is a string that needs to be quoted in the SQL statement, you need to either use single-quotes (as putting a double quote would close the string, rather than add the quote to it) or escaped double quotes.

$sql = "... WHERE suburb='{$suburb[$counter]}'";
$sql = "... WHERE suburb=\"{$suburb[$counter]}\"";

@chazzy69
This, in no way related to the previous error, is because the second "pcode" you are trying to fetch an element from is not designated as a variable.

All variables start with a dollar-sign ($). That is how PHP recognises variables. If you forget it, PHP will assume it is either a constant, or a string. (Always the latter if it happens to be inside a string.)

[chazzy69]

Thanks heaps, i think that sorts out the problems i was having :)