Having problems searching my table (mySQL)

how come I can not search info from the table in my database?

Page 1 of 1

2 Replies - 1057 Views - Last Post: 04 July 2008 - 08:49 AM

#1 firers_3  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 21-June 08

Having problems searching my table (mySQL)

Posted 21 June 2008 - 11:20 AM

I am recently building a website using database. I purchased domain from hostmonster (Apache based server) and set up a database and a TABLE through its control panel . I am sure my php code can access the database but I just can not use the code to search and display results from the TABLE content in the database. Anyone can help see whether the php code is wrong or possibly my table is not invalid. Thanks.

Here is the code for result.php

<? 
  $hostname = "localhost"; // Our DB server.
  $username = "xxx"; // The username you created for this database.
  $password = "xxx"; // The password you created for the username.
  $usertable = "cust"; // The name of the table you made.
  $dbName = "xxx"; // This is the name of the database you made.
  
  MYSQL_CONNECT($hostname, $username, $password) OR DIE("DB connection unavailable");
  @mysql_select_db( "$dbName") or die( "Unable to select database");
  ?>
  
  <?
  
  //error message (not found message)begains
  $XX = "No Record Found, to search again please close this window";
  //query  table begins
  $query = mysql_query("SELECT * FROM cust WHERE [color=#FF0000]the table[/color] LIKE '%$search%' LIMIT 0, 30");
  while ($row = @mysql_fetch_array($query))

 $variable1=$row["id"];
 $variable2=$row["name"];
 $variable3=$row["telephone"];
 
//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!!
  
  //end
  
  ?>



Here is the code for search form:

<form method="post" action="http://www.domainname/result.php" target="_blank">
<div align="center">
<table border="0" cellpadding="0" cellspacing="0">
<tr>
<td bordercolor="#000000">
<p align="center">
<select name="name" size="1">
<option value="name">Name</option>
<option value="telephone">Telephone</option>
<option value="birthday">Birthday</option>
</select> <input type="text" name="search" size="25">  <br>
Search database: <input type="submit" value="Go!!" name="Go"></p>
</td>
</tr>
</table>
</div>
</form>




Many thanks :^: :^:

Is This A Good Question/Topic? 0
  • +

Replies To: Having problems searching my table (mySQL)

#2 pertheusual  Icon User is offline

  • D.I.C Head

Reputation: 10
  • View blog
  • Posts: 245
  • Joined: 26-January 08

Re: Having problems searching my table (mySQL)

Posted 22 June 2008 - 10:52 AM

<? 
  $hostname = "localhost"; // Our DB server.
  $username = "xxx"; // The username you created for this database.
  $password = "xxx"; // The password you created for the username.
  $usertable = "cust"; // The name of the table you made.
  $dbName = "xxx"; // This is the name of the database you made.
  
//lower case. this might work, but it's super ugly
  MYSQL_CONNECT($hostname, $username, $password) OR DIE("DB connection unavailable");

//Don't need the quotes around $dbName, though it doesn't actually matter
  @mysql_select_db( "$dbName") or die( "Unable to select database");
  ?>
  
  <?
  
//Why put the error message in a string instead of just printing it properly?
  //error message (not found message)begains
  $XX = "No Record Found, to search again please close this window";
  //query  table begins

//A few errors here. You use $search, but that's not a variable, the search variable from your form is $_POST['search']
//also, add "or die(mysql_error());" That way it will tell you if the query fails.
//also, what is "the table"? There should be an actual column id in there. I'm guessing $_POST['name']
  $query = mysql_query("SELECT * FROM cust WHERE the table LIKE '%$search%' LIMIT 0, 30") or die(mysql_error());


//You forgot to put curly brackets around the while loop, so even if you did get results, it wouldn't work
//Also, since you use $row['id'] and such, you need to use mysql_fetch_assoc
  while ($row = @mysql_fetch_assoc($query)){

 $variable1=$row["id"];
 $variable2=$row["name"];
 $variable3=$row["telephone"];
 
//table layout for results


//no real reason to bother assigning the $variable variables. you can just use $row['id'] and such right in the print statements.
print ("<tr>");
print ("<td>$variable1</td>");
print ("<td>$variable2</td>");
print ("<td>$variable3</td>");
print ("</tr>");
}
  
  //below this is the function for no record!!
  
  //end
  
  ?>



I've made comments in your code about a bunch of things. I just want to comment that if you are just starting with PHP and SQL, make sure you read up on mysql injections and such. I have no idea what you plan on doing in the future, but figured I'd mention it.

Good luck,

Per
Was This Post Helpful? 0
  • +
  • -

#3 firers_3  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 21-June 08

Re: Having problems searching my table (mySQL)

Posted 04 July 2008 - 08:49 AM

Thanks alot. it helps :D :D
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1