Connecting Tech Pros Worldwide Help | Site Map

Select statement with arrays

  #1  
Old June 27th, 2009, 03:38 AM
Familiar Sight
 
Join Date: Sep 2007
Posts: 174
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.

Expand|Select|Wrap|Line Numbers
  1. while($row= mysql_fetch_array($result)){
  2. $name[$i] = $row['name']
  3. $suburb[$i] = $row['suburb']
  4. }
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]" ;


Expand|Select|Wrap|Line Numbers
  1. for ($counter=0; $counter < $numtotal; $counter++){
  2. $con = mysql_connect("localhost","user_name","pass");
  3.         if (!$con)
  4.           {
  5.  
  6.               die('Could not connect: ' . mysql_error());
  7.           }
  8.  
  9.         else {
  10.             echo "We have a connection" . "<br />";
  11.             mysql_select_db("name_database", $con);
  12.  
  13.  
  14. //this is where i seem to be getting the errors from            
  15. $sql = "SELECT var1, var2, var3 FROM table_name WHERE suburb="$suburb['$counter']" ;
  16.  
  17.  
  18.         $result = mysql_query($sql,$con);
  19.         if (!$result)
  20.           {
  21.  
  22.               die('Error: ' . mysql_error());
  23.           }
  24.  
  25. $NUMlistings = 0;
  26.         while($row = mysql_fetch_array($result))
  27.           {
  28.  
  29.          $var1[$NUMlistings] = $row['var1'];
  30.          $var2[$NUMlistings] = $row["var2"];
  31.         $var3[$NUMlistings] = $row["var3"];
  32.          $NUMlistings++;
  33.         }
  34.  
  35.         mysql_close($con);
  36.  
  37.         }
So if anyone can give us a hint or perhaps point me in the right direction, thanks for anyhelp :)
  #2  
Old June 27th, 2009, 05:01 PM
Atli's Avatar
Moderator
 
Join Date: Nov 2006
Location: Iceland
Posts: 3,701
Provided Answers: 4

re: Select statement with arrays


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.
  #3  
Old June 28th, 2009, 05:12 AM
Familiar Sight
 
Join Date: Sep 2007
Posts: 174

re: Select statement with arrays


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

Expand|Select|Wrap|Line Numbers
  1. $i = 0;
  2. while($row= mysql_fetch_array($result)){
  3.  $name[$i] = $row['name']
  4.  $suburb[$i] = $row['suburb']
  5.  $i++;  
  6. }
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
  #4  
Old June 28th, 2009, 05:38 AM
Familiar Sight
 
Join Date: Sep 2007
Posts: 174

re: Select statement with arrays


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

First this works-

Expand|Select|Wrap|Line Numbers
  1. $state[0] = "nsw";
  2. $state[1] = "qld";
  3. $pcode[0] = 2000;
  4. $pcode[1] = 3000;
  5. $suburb[0]= "sydney";
  6. $suburb[1]= "surfers paridise";
  7.  
  8. for ($i=0; $i < 2; $i++){
  9. $con = mysql_connect("localhost","user_name","pass");
  10.         if (!$con)
  11.           {
  12.  
  13.               die('Could not connect: ' . mysql_error());
  14.           }
  15.  
  16.         else {
  17.             echo "We have a connection" . "<br />";
  18.             mysql_select_db("name_table", $con);
  19.  
  20.  
  21.  
  22.             $sql = "SELECT var1, var2, var3 FROM latest_listings WHERE state='$state[$i]' AND suburb='$suburb[$i]'";
  23. .
  24. .
  25. .
  26. .

but this wont work -

Expand|Select|Wrap|Line Numbers
  1. $state[0] = "nsw";
  2. $state[1] = "qld";
  3. $pcode[0] = 2000;
  4. $pcode[1] = 3000;
  5. $suburb[0]= "sydney";
  6. $suburb[1]= "surfers paridise";
  7.  
  8. for ($i=0; $i < 2; $i++){
  9. $con = mysql_connect("localhost","user_name","pass");
  10.         if (!$con)
  11.           {
  12.  
  13.               die('Could not connect: ' . mysql_error());
  14.           }
  15.  
  16.         else {
  17.             echo "We have a connection" . "<br />";
  18.             mysql_select_db("name_table", $con);
  19.  
  20.  
  21.  
  22.             $sql = "SELECT var1, var2, var3 FROM latest_listings WHERE state='$state[$i]' AND suburb='$suburb[$i]' AND pcode=pcode[$i]";
  23. .
  24. .
  25. .
  26. .
i know it wont work because of this line -

Expand|Select|Wrap|Line Numbers
  1. 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,
  #5  
Old June 28th, 2009, 09:59 AM
Atli's Avatar
Moderator
 
Join Date: Nov 2006
Location: Iceland
Posts: 3,701
Provided Answers: 4

re: Select statement with arrays


Quote:
Originally Posted by chazzy69 View Post
Anyway the line im having trouble with is -

$sql = "SELECT var1, var2, var3 FROM table_name WHERE suburb="$suburb[$counter]" ;
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:
Expand|Select|Wrap|Line Numbers
  1. $sql = "SELECT var1, var2, var3 
  2.         FROM table_name 
  3.         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.
Expand|Select|Wrap|Line Numbers
  1. $sql = "... WHERE suburb='{$suburb[$counter]}'";
  2. $sql = "... WHERE suburb=\"{$suburb[$counter]}\"";
Quote:
Originally Posted by chazzy69 View Post
i know it wont work because of this line -

Expand|Select|Wrap|Line Numbers
  1. AND pcode=pcode[$i]
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.)
  #6  
Old June 29th, 2009, 01:48 AM
Familiar Sight
 
Join Date: Sep 2007
Posts: 174

re: Select statement with arrays


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


Similar Threads
Thread Thread Starter Forum Replies Last Post
Having trouble with arrays poreko answers 5 December 29th, 2008 03:01 AM
Updating Multiple Records Based On Records Returned By A Select Statement willwmagic answers 2 February 15th, 2007 05:46 PM
Doing Calculations with arrays outstretchedarm answers 4 September 13th, 2006 06:05 PM
ASP / SQL Query - Conditional SELECT Statement Guy Hocking answers 7 July 19th, 2005 11:18 AM