mysql fetch array

[alena1347]

I am trying to build search option in which I want to match multiple options with database values

1)When using the "where IN" clause the values compared are 'OR'd' and i need it 'AND' the array.

 

 

2)

$do=mysql_query("SELECT * from hire WHERE sname=$sname AND lhname IN ($ser2) AND exnum IN ($ser3)");

while($row2=mysql_fetch_array($do))

{

?>

<tr>

<td><?php echo $row2['id']; ?></td>

<td><?php echo $row2['fname']; ?></td>

<td><?php echo $row2['lname']; ?></td>

<td><?php echo $row2['lhname']; ?></td>

<td><?php echo $row2['cnum']; ?></td>

<td><?php echo $row2['exnum']; ?></td>

<td><?php

$id=$row2['id'];

$row3=mysql_query("SELECT * from skill where id=$id");

while($val=mysql_fetch_array($row3))

{

$bits=$val['sname'];

$arr[]=$bits;

}

$imp=implode(",",$arr);

echo $imp ; ?></td>

 

</tr>

 

 

The above code gives Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given

 

at the while loop please help

 

Thank you!

[Psycho]

That error

Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given

 

is a sure sign that your query failed. the result will be a Boolean false and not a DB result resource - thus the error. You need to check for errors - don't assume you write the code correctly the first time. Plus, there is absolutely no reason to use the IN clause if you are only going to have one value. And, you do not have quotes around the search values. Unless these are number values it will cause this type of error. Lastly, never run queries in loops - there is always a better way. In this case the better way is with a JOIN query the GROUP_CONCAT function.

Edited February 1, 2013 by Psycho

[Psycho]

$query = "SELECT h.id, h.fname, h.lname, h.lhname, h.cnum, h.exnum,
			 GROUP_CONCAT(s.sname SEPARATOR ', ') AS snames
	  FROM hire AS h
	  LEFT JOIN skill AS s USING(id)
	  WHERE h.sname='$sname'
	    AND h.lhname = '$ser2'
	    AND h.exnum = '$ser3'
	  GROUP BY h.id";
$result = mysql_query($query);

if(!$result)
{
   echo "Query Failed!<br>\n";
   echo "Query: {$query}<br>\n";
   echo "Error: " . mysql_error();
}
else
{
   while($row = mysql_fetch_assoc($result))
   {
    echo "<tr>\n";
    echo "<td>{$row['id']}</td>\n";
    echo "<td>{$row['fname']}</td>\n";
    echo "<td>{$row['lname']}</td>\n";
    echo "<td>{$row['lhname']}</td>\n";
    echo "<td>{$row['cnum']}</td>\n";
    echo "<td>{$row['exnum']}</td>\n";
    echo "<td>{$row['snames']}</td>\n";
    echo "</tr>\n";
   }
}

Edited February 1, 2013 by Psycho

[alena1347]

That error

 

 

is a sure sign that your query failed. the result will be a Boolean false and not a DB result resource - thus the error. You need to check for errors - don't assume you write the code correctly the first time. Plus, there is absolutely no reason to use the IN clause if you are only going to have one value. And, you do not have quotes around the search values. Unless these are number values it will cause this type of error. Lastly, never run queries in loops - there is always a better way. In this case the better way is with a JOIN query the GROUP_CONCAT function.

 

 

 

 

The $ser2 is a implode of an array

The $ser3 is also an implode of an array for the lhname and exnum

Thank you

[Psycho]

The $ser2 is a implode of an array

The $ser3 is also an implode of an array for the lhname and exnum

Thank you

 

I thought that is what you meant at first - but then your first question makes no sense

1)When using the "where IN" clause the values compared are 'OR'd' and i need it 'AND' the array.

 

That would never return a match. For example, let's say the values within the IN clause are like this

WHERE field IN ('apple', 'banana', 'cucumber')

It is impossible for any ONE field value to match multiple comparison values. It could be 'apple' OR it could be 'banana', but it could never be 'apple' AND 'banana'

 

If those vars are PROPERLY formatted comma separated strings - then go back to using the IN clause. If that does not get the results you want then you need to try and explain what you want in a different way (perhaps provide some examples).

 

$query = "SELECT h.id, h.fname, h.lname, h.lhname, h.cnum, h.exnum,
			 GROUP_CONCAT(s.sname SEPARATOR ', ') AS snames
	 FROM hire AS h
	 LEFT JOIN skill AS s USING(id)
	 WHERE sname='$sname'
	 AND lhname IN ($ser2)
	 AND exnum IN ($ser3)
	 GROUP BY h.id";
$result = mysql_query($query);

if(!$result)
{
echo "Query Failed!<br>\n";
echo "Query: {$query}<br>\n";
echo "Error: " . mysql_error();
}
else
{
while($row = mysql_fetch_assoc($result))
{
 echo "<tr>\n";
 echo "<td>{$row['id']}</td>\n";
 echo "<td>{$row['fname']}</td>\n";
 echo "<td>{$row['lname']}</td>\n";
 echo "<td>{$row['lhname']}</td>\n";
 echo "<td>{$row['cnum']}</td>\n";
 echo "<td>{$row['exnum']}</td>\n";
 echo "<td>{$row['snames']}</td>\n";
 echo "</tr>\n";
}
}

Edited February 1, 2013 by Psycho