Jump to content

mysql fetch array


alena1347

Recommended Posts

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!

Link to comment
Share on other sites

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 by Psycho
Link to comment
Share on other sites

$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 by Psycho
Link to comment
Share on other sites

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

Link to comment
Share on other sites

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 by Psycho
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.