Help with use of the value of an array element in a "WHERE" clause

Thank you in advance for any help with this.

I am trying to use the value of an array element as a condition in a database query. The array $geo is an array of -

col [0] - Country codes, such as usa, can, mex, etc.
col [1] - Names of states/provinces spelled out (New York, Alberta, Jalisco, etc.)
col [3] - The abbreviation for the state/province (ny, ab, jal, etc)

The database table called event_info currently contains test information about events in all the states of the United States. I want the query to tell me
how many events (the $num_rows) there are in each of the states/provinces. I don't want to return the information from the table - not here. Here's the
code that I have written to first get the abbreviation for the state from the array $geo, run the query and then get the count of the events.


//Use the info in the array $geo to query the DB for events in the various states, so open the DB...
// Open connection to the "events" database
mysql_connect("localhost", "blahblah", "starbucksisbad") or die("Failure to communicate with database: ' . mysql_error()");
//Open the "events" database
mysql_select_db("officialeventlocator_com_events") or die ("Could not open database login. Error: " . mysql_error());
print "<br />";
$num_rows= "";

for ($rrow = 0; $rrow < (count($geo)); $rrow++)
{
$result_count = mysql_query("SELECT * FROM event_info WHERE event_state = '$geo[$rrow][2]' ");
//print 'Running query now.' ;
print $geo[$rrow][0].' '.$geo[$rrow][2];
$num_rows = mysql_num_rows( $result_count);
print " $num_rows was returned. ";
print "<br />";
}

print "<br />";


ADULT CONTENT WARNING - Although you will not see any of the test data, please be aware that, when completed, the site will be a directory of adult events. If you'd rather not participate for that
reason, please stop here...

The results of the above are visible at www.officialeventlocator.com/bdsm/clickable_block.php.

For some reason, no state returns a count, even though there are 70+ test events in the DB; all of them are in states that are/should be tested for the existence of events. I believe that the query is not properly reading the value of '$geo[$rrow][2]'. I have fiddled with punctuation, parentheses, braces and things like that, but I have run out of ideas. The values seem to print OK, so the print statement understands the value/s of the array elements.

Can anyone point me at what is surely a stupid mistake that lies in the code? Many thanks to all. I'll be back...


Steve E.

 

I think your first step would be to echo the generated SQL query and debug that. You could also add or die(mysql_error()); at the end of your query call to display any errors if needed.

Cheers,
Mike

 

Hi

Try wrapping braces { } around the PHP array in your query.

$result_count = mysql_query("SELECT * FROM event_info WHERE event_state = '{$geo[$rrow][2]}' "); 

PHP:

If that doesn't work make sure the query is valid by printing it out -

$query = "SELECT * FROM event_info WHERE event_state = '{$geo[$rrow][2]}' "; 
echo 'Generated Query is: '. $query . '<br />';
$result_count = mysql_query($query);

PHP:

Regards,
Steve

 

Thanks to Mike and to Steve136 for their replies...wrapping the $geo array element in the braces, as described, worked - now I am sure that the script works so far. So I can move on. I thought that I had tried this combination of punctuation before, but I guess not. Thanks again.

Steve E.