Problem with simple query

[JerryDi]

I am having a problem with this simple query. The query runs fine in phpMyAdmin but does not generate a result when using this php script. The user generates a search [name1] from a separate html form. Any ideas ? Thanks

<table width="790" border="0", cellpadding="0" cellspacing="0"background="/members/Pictures/ceder.jpg" >
  <tr valign="top" > 
    <td width="90"><font size="+2">Year</td>  
    <td width="385"><font size="+2">Competition</td>  
    <td width="315"><font size="+2">Winner</td>
  </tr>

</tr>        

<?php
$hostname = "localhost"; // DBserver.
$username = "axxxxxxx_Jerry"; // DBusername
$password = "zzzzzzzz"; // DBpassword
$usertable = "exp_channel_data"; // TBLname
$dbName = "xxxxzzzz"; // DBname

$conn = mysql_connect("localhost","axxxxxx_Jerry","xxxxxx") or die ('Error connecting to mysql');
$dbName = 'xxxxzzzz';
mysql_select_db($dbName);

echo "Connected to Database, searching for winner named ";
echo $_POST['name1']
?>
<?php
//error message (not found message)begins
$XX = "No Record Found, to search again please close this window";
//query details table begins



$q_str = "SELECT field_id_8, field_id_7, field_id_6 FROM exp_channel_data WHERE field_id_8 LIKE '%$name1%'";
while ($row = @mysql_fetch_array($query))
{

$variable1=$row["field_id_6"];
$variable2=$row["field_id_7"];
$variable3=$row["field_id_8"];
//table layout for results

print ("<tr>");
print ("<td>$variable1</td>");
print ("<td>$variable2</td>");
print ("<td>$variable3</td>");
print ("</tr>");
}
//below this is the function for no record!!
if (!$variable3)
{
print ("$XX");
}
//end
?>
</table>

[james438]

please remove your passwords!

Just a thought, but try replacing $query with $q_str. and remove the @ to make errors visible. Also add:

$name1 = $_POST['name1'];

after

echo $_POST['name1'];

and replace:

$variable1=$row["field_id_6"]; 
$variable2=$row["field_id_7"]; 
$variable3=$row["field_id_8"];

with

$variable1=$row['field_id_6']; 
$variable2=$row['field_id_7']; 
$variable3=$row['field_id_8'];

there are some other errors, but they are rather minor.

[JerryDi]

Hi James
I have made these changes but unfortunately it's behaving exactly the same; could you suggest anything ?
thanks

[james438]

Could you post your updated code and a sample row from your table that should be showing up with your query? I want to see if I can recreate your problem on my end and also to see if you made the performed the changes I suggested correctly.

[JerryDi]

Here is the code:

<table width="790" border="0", cellpadding="0" cellspacing="0"background="/members/Pictures/ceder.jpg" >
  <tr valign="top" > 
    <td width="90"><font size="+2">Year</td>  
    <td width="385"><font size="+2">Competition</td>  
    <td width="315"><font size="+2">Winner</td>
  </tr>
</tr>       

<?php
$hostname = "localhost"; // DBserver.
$username = "username"; // DBusername
$password = "password"; // DBpassword
$usertable = "exp_channel_data"; // TBLname
$dbName = "database"; // DBname

$conn = mysql_connect("localhost","username","password") or die ('Error connecting to mysql');
$dbName = 'database';
mysql_select_db($dbName);

echo "Connected to Database, searching for winner named ";
echo $_POST['name1'];
$name1 = $_POST['name1'];
?>
<?php
//error message (not found message)begins
$XX = "No Record Found, to search again please close this window";
//query details table begins

$q_str = "SELECT field_id_8, field_id_7, field_id_6 FROM exp_channel_data WHERE field_id_8 LIKE '%$name1%'";
while ($row = @mysql_fetch_array($q_str))
{

$variable1=$row['field_id_6'];
$variable2=$row['field_id_7'];
$variable3=$row['field_id_8'];
//table layout for results

print ("<tr>");
print ("<td>$variable1</td>");
print ("<td>$variable2</td>");
print ("<td>$variable3</td>");
print ("</tr>");
}
//below this is the function for no record!!
if (!$variable1)
{
print ("$XX");
}
//end
?>
</table>

You can try it at: http:www.aegc.co.uk/members/search-form.html. Try searching on Dixon [this should produce about 15 rows but comes up empty]
many thanks

[liamallan]

try removing @ in:

while ($row = @mysql_fetch_array($q_str)) 

[JerryDi]

doing this produces the following error

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource

[djr33]

That means that either there is an error in the query (it is invalid) or that it doesn't match your database: for example, you may have a typo in one of the column names or you may be looking for a data type (integer, for example) in a column that doesn't support it (maybe only text, for example).

[JerryDi]

Hi
I knew there was nothing wrong with the query so I tried a different approach and BINGO it works
Here is the code

<html>
<body>
<?php
$username=" ";
$password="  ";
$database=" ";

mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");


echo "Connected to Honours Board Database....... results for winner named ";
echo $_POST['name1'];
$name1 = $_POST['name1'];

$query="SELECT field_id_8, field_id_7, field_id_6 FROM exp_channel_data WHERE field_id_8 LIKE '%$name1%' ORDER BY field_id_7 ASC, field_id_6 DESC";
$result=mysql_query($query);

$num=mysql_numrows($result);

mysql_close();
?>
<table width="690" border="0", cellpadding="0" cellspacing="0" background="/members/Pictures/ceder.jpg" >
<tr>
<th><div align="left"><font face="Arial, Helvetica, sans-serif">Year</font></div></th>
<th><div align="left"><font face="Arial, Helvetica, sans-serif">Competition</font></div></th>
<th><div align="left"><font face="Arial, Helvetica, sans-serif">Winner</font></div></th>
</tr>
<?php
$i=0;
while ($i < $num) {

$f1=mysql_result($result,$i,"field_id_6");
$f2=mysql_result($result,$i,"field_id_7");
$f3=mysql_result($result,$i,"field_id_8");
?>
<tr> 
  <td><font face="Arial, Helvetica, sans-serif"><?php echo $f1; ?></font></td>
  <td><font face="Arial, Helvetica, sans-serif"><?php echo $f2; ?></font></td>
  <td><font face="Arial, Helvetica, sans-serif"><?php echo $f3; ?></font></td>
</tr>

<?php
$i++;
}
?>
</body>
</html>

Thanks for the help