How to use mySQL WHERE clause in PHP?

Question

Can't seem to pass anything from the "securityinfo" field.

for ($k = 1; $k < $fieldscnt; $k++)
    {
    $selectsecurityname = mysql_query("SELECT `security name` FROM securityinfo WHERE symbol = '$symbol[$k]'") or die(mysql_error());
    $securityname = mysql_fetch_array($selectsecurityname);
    $sym = $symbol[$k]

    echo "<td>$securityname[$sym]</td>";

Basically, I want to to search for the row that has a match in the "symbol" field, then return the value from the "securityinfo" field in the same row. Any suggestions would be greatly appreciated

Answer 1

$securityname is not full array immediately. If you'd like to pull out values via a key, we need to cycle through each returned row and add it to our own array:

while ($row = mysql_fetch_array($selectsecurityname) {
  $myArray[] = $row;
}

$sym = $symbol[$k];
echo "<td>".$myArray[$sym]."</td>";

For more information on mysql_fetch_array(), see the documentation: http://php.net/manual/en/function.mysql-fetch-array.php

Answer 2

PHP has some limitations on how you can use variables inside quoted strings. So you can't get array elements like you are doing.

Also you should be sure to use escaping when you interpolate variables into SQL expressions like that.

$selectsecurityname = mysql_query("
    SELECT `security name` FROM securityinfo 
    WHERE symbol = '" . mysql_real_escape_string($symbol[$k]) . "'") 
  or die(mysql_error());

Or if it makes more sense to do it this way:

$sym = mysql_real_escape_string($symbol[$k]);
$selectsecurityname = mysql_query("
    SELECT `security name` FROM securityinfo 
    WHERE symbol = '{$sym}'") 
  or die(mysql_error());

Answer 3

If you want to access array indexes in a string, enclose them in curly braces:

$a= array(
 'hello'=> 'world',
 'nice'=> 'one',
);

var_dump("This is the value of \$a['hello']: {$a['hello']}");

And re-write your original statement as:

for ($k = 1; $k < $fieldscnt; $k++)
    {
    $selectsecurityname = mysql_query("SELECT `security name` FROM securityinfo WHERE symbol = '{$symbol[$k]}'") or die(mysql_error());
    $securityname = mysql_fetch_array($selectsecurityname);
    $sym = $symbol[$k]

    echo "<td>{$securityname[$sym]}</td>";

Of course, you'll need to make sure that $symbol[$k] is escaped correctly for use in a mysql statement.

Answer 4

Besides the answers of the others fixing parts of your example, there is another problem:
You (try to) grab a value from a table in your example. But when using it you access it as an array while it probably is just a string (a name).

A complete example with all things fixed would look like this:

for ($k = 1; $k < $fieldscnt; $k++)
{
    $sym = mysql_real_escape_string($symbol[$k]);
    $selectsecurityname = mysql_query("SELECT `security name` FROM securityinfo WHERE symbol='$sym'") or die(mysql_error());
    $securitynamearray = mysql_fetch_array($selectsecurityname);
    $securityname = $securitynamearray['security name'];

    echo "<td>$securityname</td>";
}

PS Curly brackets are not required for regular variables, so I omitted them.

PPS You could use mysql_fetch_row instead of mysql_fetch_array to prevent (eventually) problems with your fieldname and the space it contains:

$securitynamerow = mysql_fetch_row($selectsecurityname);
$securityname = $securitynamerow[0];