[Resolved] Possible to avoid using mysql_results()?

jakeone

The following works but I'm doubtful of its efficiency. For one, I'm using mysql_results which the books warn against. Is there a simpler way to achieve the same using, say, mysql_fetch_array?

$sql_vac = "SELECT vacID FROM vacancies WHERE UNIX_TIMESTAMP(vacDeadline) < UNIX_TIMESTAMP(CURRENT_DATE())";
$result_vac = mysql_query($sql_vac);

if ($result_vac)
{
// build root of SQL statement
$sql_deleteVac = "DELETE FROM vacancies WHERE vacID = ";

$num_vacs = mysql_num_rows($result_vac);

for ($i=0; $i<$num_vacs; $i++)

{
if ($i==0)
{
$sql_deleteVac .= mysql_result($result_vac, $i, "vacID");
}
else
{
$sql_deleteVac .= " OR vacID = ".mysql_result($result_vac, $i, "vacID");
}
}
}
echo $sql_deleteVac;

Mordecai

Sure. Look ut [man]mysql_fetch_array/man for an example. You may also want [man]mysql_fetch_assoc/man instead--it's just the associative array--every key is named after its column name.

Edit: must be the mountain air (wait, what mountain?), but I said the wrong function. D'oh! (Changed row to assoc)

jakeone

I consulted the PHP manual before posting. Sorry if I'm being slow but I couldn't find an example that I could get to work.

Before posting, I tried using mysql_fetch_array in the FOR loop:

... same previous code...
$qry_vac = mysql_fetch_array($result_vac);
for ($i=0; $i<$num_vacs; $i++)
{
if ($i==0)
{
$sql_deleteVac .= $qry_vac['vacID'];
}
else
{
$sql_deleteVac .= " OR vacID = ".$qry_vac['vacID'];
}
... etc ...

but the value of $qry_vac['vacID'] did not change with each loop.

I guess I'm looking for a "mysql_fetch_array equivalent" of mysql_result($result_vac, $i, "vacID").

tsinka

Hi,

use a while loop with mysql_fetch_array:

$i=0;
while ($qry_vac = mysql_fetch_array($result_vac)) {
  $vacID = $qry_vac['vacID'];
  $sql_deletVac .= ($i>0 ? " OR ":"")." vacID='$vacID'";
  $i++;
}

Thomas

jakeone

Works great. Thanks Thomas.

devinemke

just my $.02. i do use mysql_result but only when i am doing a query where i only expect one field of one record to be returned. like just the email address if id #321:

$result = mysql_query('SELECT email FROM users WHERE id = 321');
$email = mysql_result($result, 0);