[Resolved] updating search via checkbox

[vtbruinsfan]

Hi All.

I have a mysql customer database that I'm building with a PHP frontend. What I want is when someone searches the database via, say the last name, and multiple choices come up, I want the user to be able to select the intended customer and then have that customers data presented in full. How do I accomplish this?

TIA,

Denise

[chriswheat]

what is the table name and all of the fields in the table?

[vtbruinsfan]

Thanks for the quick reply.

Table name is 'contacts' and fields are: last_name, first_name, address, address2, city, state, zip, phone, comments.

Denise

[helz]

i suggest that you add an auto-incrementing ID field to the database as it makes it easier to pull information from the database. the example below includes the ID field...

if ($_SERVER['REQUEST_METHOD'] == "POST") {//if user has posted form perform check
  $lastname = $_POST['lastname'];//grab variable from $_POST array
  $query = mysql_query("SELECT first_name AND city FROM contacts WHERE last_name='$lastname'");// get user info from the database
  $noofsearchresults = mysql_num_rows($query) or die(mysql_error());//get number of results
  if ($noofsearchresults == 0) {//search returned 0 hits
    echo "Your Search for Customer Named: <i>$lastname</i> Returned 0 Results! Please Try Again!";
  } else {
     echo "Your Search for Customer Named: <i>$lastname</i> Returned <i>$noofsearchresults</i> Results!<hr width=50%>";
    while ($result = mysql_fetch_array($query)) {//start loop to display links to each customer
      extract($result);//get all info from database for that user
      echo "First Name: <i>$first_name</i> City: <i>$city</i><a href='".$_SERVER['PHP_SELF']."?custid=$custid'>Link to their info</a><br>";
    }
} elseif (!empty($_GET['custid'])) {//user has selected a customer to view
   $grabinfo = mysql_query("SELECT * FROM contacts WHERE custid='".$_GET['custid']."' ") or die(mysql_error());
   $grabinfo = mysql_fetch_array($grabinfo);//can use the same variable again since we are only grabbing one user's info from the db
   extract($grabinfo);
   echo "Users ID: $custid<br>
             First Name: $first_name<br>
             Last Name: $last_name<br>
             Address: $address<br>
             Address2: $address2<br>
             City: $city<br>
             State: $state<br>
             Zip: $zip<br>
             Phone: $phone<br>
             Comments: $comments<br>";

} else {//first visit to page so display form
   //display form
   echo "Please enter the last name of the customer below:<br>
             <form method='post' action='".$_SERVER['PHP_SELF']."'>\n
             Last Name: <input type='text' name='lastname'><br>
             <input type='submit' value='Search Database!'>
             </form>";
}

[chriswheat]

First on your database you should add a field called ID make it type: int
extra: auto_incrament
and make it primary

so that when you add a contact it will have a unique ID, this way you can display information on wheat/chris instead of wheat/john

now make a page called search.php or what ever and insert the form:
<form method="get" action="search_results.php">
<p><input type="text" name="search" size="20"><input type="submit" value="Submit" name="B1"></p>
</form>

make a page called search_results.php and put in the following code:

$dbhost = "localhost";
$dbusername = "yourusername";
$dbpasswd = "yourpassword";
$database_name = "your data base name";
$connection = mysql_pconnect("$dbhost","$dbusername","$dbpasswd")or die ("Could not connect to MYSQL.");
$db = mysql_select_db("$database_name", $connection)or die("Could not connect to Database.");

$sql = mysql_query("SELECT * FROM contacts WHERE last_name='$search'");
if(mysql_num_rows($sql) > 0) {
// If there are rows, make the table
echo "<table border=\"0\" cellpadding=\"5\" cellspacing=\"0\" width=\"100%\">
<tr>
<td width=\"25%\" style=\"border-bottom-style: solid\">Last Name</td>
<td width=\"25%\" style=\"border-bottom-style: solid\">First Name</td>
<td width=\"25%\" style=\"border-bottom-style: solid\">Phone #</td>
<td width=\"25%\" style=\"border-bottom-style: solid\">&nbsp;</td>
</tr>";
while($row=mysql_fetch_array($sql)) {
// adds the lastname then firstname the phone number then link to detail
echo " <tr><td width=\"25%\">".$row['last_name']."</td>
<td width=\"25%\">".$row['first_name']."</td>
<td width=\"25%\">".$row['phone']."</td>
<td width=\"25%\"><a href=\"more_info.php?id=".$row['id']."\">View Detail</a></td></tr>";
}
echo "</table>";
}

now make a file called more_info.php and put in the following code:

$dbhost = "localhost";
$dbusername = "yourusername";
$dbpasswd = "yourpassword";
$database_name = "your data base name";
$connection = mysql_pconnect("$dbhost","$dbusername","$dbpasswd")or die ("Could not connect to MYSQL.");
$db = mysql_select_db("$database_name", $connection)or die("Could not connect to Database.");

$sql = mysql_query("SELECT * FROM contacts WHERE id='$id'");
$result = mysql_fetch_array($sql);

echo " <table border=\"0\" cellpadding=\"5\" cellspacing=\"0\" width=\"100%\">
<tr>
<td width=\"50%\">Last Name:</td>
<td width=\"50%\">".$row['last_name']."</td>
</tr>
<tr>
<td width=\"50%\">First Name:</td>
<td width=\"50%\">".$row['first_name']."</td>
</tr>
<tr>
<td width=\"50%\">Phone:</td>
<td width=\"50%\">".$row['phone']."</td>
</tr>
<tr>
<td width=\"50%\">Address:</td>
<td width=\"50%\">".$row['address']."</td>
</tr>
<tr>
<td width=\"50%\">Address 2:</td>
<td width=\"50%\">".$row['address2']."</td>
</tr>
<tr>
<td width=\"50%\">City:</td>
<td width=\"50%\">".$row['city']."</td>
</tr>
<tr>
<td width=\"50%\">State:</td>
<td width=\"50%\">".$row['state']."</td>
</tr>
<tr>
<td width=\"50%\">Zip:</td>
<td width=\"50%\">".$row['zip']."</td>
</tr>
<tr>
<td width=\"50%\">Comments:</td>
<td width=\"50%\">".$row['comments']."</td>
</tr>
</table>";

That should do it! I have not tested the code, but you get the idea.

Chris

[chriswheat]

I would go with "helz's" code it appears to be cleaner! I just learned a new way of extracting / displaying information. 😃

Gotta love these forums!
Chris

[helz]

just make sure that you understand it before copying.

for some of my pages where i was getting alot of fields from the database, instead of typing:
$var1 = $result['var1'];
$var2 = $result['var2'];
etc...
i found out about the extract() which does it for all the variables, problem is that if you use SELECT * then it will do all the fields and not certain ones. if you are only using a small number of fields then you can limit the results grabbed from the db in the mysql query

[vtbruinsfan]

Thanks for the code, Helz...but I'm getting a parse error at

} elseif (!empty($_GET['custid'])) {

Your thoughts?

Thanks,

Denise

[helz]

well its not that line, i missed out an }
i have only closed the while loop and not the else part
code snippet:

if ($noofsearchresults == 0) {//search returned 0 hits 
    echo "Your Search for Customer Named: <i>$lastname</i> Returned 0 Results! Please Try Again!"; 
  } else { 
     echo "Your Search for Customer Named: <i>$lastname</i> Returned <i>$noofsearchresults</i> Results!<hr width=50%>"; 
    while ($result = mysql_fetch_array($query)) {//start loop to display links to each customer 
      extract($result);//get all info from database for that user 
      echo "First Name: <i>$first_name</i> City: <i>$city</i><a href='".$_SERVER['PHP_SELF']."?custid=$custid'>Link to their info</a><br>"; 
    } 
  }//         <-------------------HERE IT IS :D

right at the bottom of the code above is where you need to add a } bracer

[vtbruinsfan]

thanks, fixed that...but now I get:

Supplied argument is not a valid MySQL result resource at this line:

$noofsearchresults = mysql_num_rows($query) or die(mysql_error());

Denise

[helz]

move the: or die(mysql_error())
to the end of the mysql_query() line...

you can try it again to see if that fixed it but i dout it.

to be honest ive had this trouble a couple of times with mysql_num_rows.
not sure whether this will work or not but here goes:

$noofsearchresults = mysql_num_rows($query);

change it to:

$noofsearchresults = mysql_num_rows(mysql_result($query));

if that does not work then this should/will:

$noofsearchresults = mysql_query("SELECT COUNT(*) FROM contacts WHERE last_name='$lastname' ");

[vtbruinsfan]

Tinkered with it a bit and got it working...thanks very much for the help!

Denise

[helz]

please click on the Mark Thread Resolved link at the bottom of this post.

Ta!