Solved

I need a cut-and-paste code page(s) for a simple PHP & MySQL search page

Posted on 2009-05-15
20
242 Views
Last Modified: 2013-12-12
Hello,

I would like to learn to do this on my own, but I find that I learn much better if I can take the thing apart and then rebuild it.

I need a page that connects to my MySQL table and lets he user select to search by "catalog#, mfgr_name, or upc_code". Can be radio button or checkbox. Then just one input field to search, where the SELECT statement can use LIKE for the input param.

Then, when the results are displayed, I need them in a table. Make the table as ugly and plain as you like, I can format the HTML output later myself, but pagination would be great.

For extra points, I'd like to make the search by manufacturer actually query the DB and generate a dropdown list of all unique (DISTINCT) manufacturers. Selecting a given manufacturer would then return a table of all DB items where mfgr_name = the dropdown selection. (250 extra points for this)

I know this is a pretty standard scenario. I have 2 Oreilly books, but not being versed enough in PHP, I dont know what to pull out or leave in from their example code.

Thanks!
0
Comment
Question by:Tortuga
  • 12
  • 8
20 Comments
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 24398686
I can give you a critical part of the answer - how to do the basic MySQL things in PHP.  Your table structure and your input data will be up to you.  Please read over the code snippet and post back here with any questions.

You might also want to consider getting the excellent SitePoint book shown here:

http://www.sitepoint.com/books/phpmysql1/

It covers this sort of form-to-mysql programming -- is excellent as a tutorial and a reference, a permanent part of my professional library.

HTH, ~Ray
<?php // RAY_mysql_example_2.php
error_reporting(E_ALL);
 
// IMPORTANT PAGES FROM THE MANUALS
// MAN PAGE: http://us2.php.net/manual/en/ref.mysql.php
// MAN PAGE: http://us2.php.net/manual/en/mysql.installation.php
 
 
// CONNECTION AND SELECTION VARIABLES FOR THE DATABASE
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";        // GET THESE FROM YOUR HOSTING COMPANY
$db_user = "??";
$db_word = "??";
 
 
// OPEN A CONNECTION TO THE DATA BASE SERVER
// MAN PAGE: http://us2.php.net/manual/en/function.mysql-connect.php
if (!$db_connection = mysql_connect("$db_host", "$db_user", "$db_word"))
{
   $errmsg = mysql_errno() . ' ' . mysql_error();
   echo "<br/>NO DB CONNECTION: ";
   echo "<br/> $errmsg <br/>";
}
 
// SELECT THE MYSQL DATA BASE
// MAN PAGE: http://us2.php.net/manual/en/function.mysql-select-db.php
if (!$db_sel = mysql_select_db($db_name, $db_connection))
{
   $errmsg = mysql_errno() . ' ' . mysql_error();
   echo "<br/>NO DB SELECTION: ";
   echo "<br/> $errmsg <br/>";
   die('NO DATA BASE');
}
// IF WE GOT THIS FAR WE CAN DO QUERIES
 
 
 
 
// CREATING A TABLE
$sql = "CREATE TEMPORARY TABLE my_table (
        _key int NOT NULL AUTO_INCREMENT,
        name varchar(24) NOT NULL DEFAULT '',
        PRIMARY KEY(_key)
        )";
$res = mysql_query($sql);
 
// IF mysql_query() RETURNS FALSE, GET THE ERROR REASONS
if (!$res)
{
   $errmsg = mysql_errno() . ' ' . mysql_error();
   echo "<br/>QUERY FAIL: ";
   echo "<br/>$sql <br/>";
   die($errmsg);
}
 
 
 
 
// ESCAPING A DATA FIELD FOR USE IN MYSQL QUERIES
// MAN PAGE: http://us2.php.net/manual/en/function.mysql-real-escape-string.php
$name = mysql_real_escape_string("O'Reilly");
 
 
 
 
// MAKING AN INSERT QUERY, USING THE ESCAPED STRING AND TESTING THE RESULTS
$sql = "INSERT INTO my_table (name) VALUES (\"$name\")";
$res = mysql_query($sql);
 
// IF mysql_query() RETURNS FALSE, GET THE ERROR REASONS
if (!$res)
{
   $errmsg = mysql_errno() . ' ' . mysql_error();
   echo "<br/>QUERY FAIL: ";
   echo "<br/>$sql <br/>";
   die($errmsg);
}
// IF WE GET THIS FAR, THE QUERY SUCCEEDED AND WE HAVE A RESOURCE-ID IN $res SO WE CAN NOW USE $res IN OTHER MYSQL FUNCTIONS
 
// GET THE AUTO_INCREMENT ID OF THE RECORD JUST INSERTED
// MAN PAGE: http://us2.php.net/manual/en/function.mysql-insert-id.php
$_key  = mysql_insert_id();
 
echo "<br/><br/>KEY: $_key AND NAME WITH ESCAPE STRING INCLUDED: $name <br/><br/>\n";
 
 
 
 
// MAKING A SELECT QUERY AND TESTING THE RESULTS
$sql = "SELECT * FROM my_table";
$res = mysql_query($sql);
 
// IF mysql_query() RETURNS FALSE, GET THE ERROR REASONS
if (!$res)
{
   $errmsg = mysql_errno() . ' ' . mysql_error();
   echo "<br/>QUERY FAIL: ";
   echo "<br/>$sql <br/>";
   die($errmsg);
}
// IF WE GET THIS FAR, THE QUERY SUCCEEDED AND WE HAVE A RESOURCE-ID IN $res SO WE CAN NOW USE $res IN OTHER MYSQL FUNCTIONS
 
// ITERATE OVER THE RESULTS SET
while ($row = mysql_fetch_assoc($res))
{
   var_dump($row);
}

Open in new window

0
 

Author Comment

by:Tortuga
ID: 24398703
I'll go through and give it a try.
I should mention, I already have a MySQL database created with about 250,000 rows. Only 5 attributes though.

Thank you.
0
 

Author Comment

by:Tortuga
ID: 24428659
Here is what I get when I just replace my connection, username, etc..:

KEY: 1 AND NAME WITH ESCAPE STRING INCLUDED: O\'Reilly

array(2) { ["_key"]=> string(1) "1" ["name"]=> string(8) "O'Reilly" }

Now what?

0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

Author Comment

by:Tortuga
ID: 24428672
I already got this far on my own: http://www.1bigfamily.info/new/test1.php

I connect to the table, and query SELECT * FROM tablename

But I want a dropdown list populated by field Manufacturer (SELECT DISTINCT). The user chooses one of the manufacturer names and click "GO". Then a table comes up of only one manufacture's items.

I just need the code to do that, please cant someone assist?

Thanks,
Tortuga
0
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 24431637
We might be able to help if you post the data base schema so we can see the names and formats of the fields.  Do you have only one table?  Do you understand the teaching example code I posted above?
0
 

Author Comment

by:Tortuga
ID: 24433684
Hi,
I understand the example that you posted. I am working with a similar bit of code already (pasted as a code snippet)

My database is only one table at present, and its all I need right now. The fields are ID (Primary Key), CATALOG, DESCRIPTION, MANUFACTURER, UPC_CODE, SKU.

When the table is generated in HTML, I need the SKU field to actually be inserted into a clickable URL, so it will look llike this: http://www.url.com/SKU. However, I know I can figure that out once I get the application working.

So to reiterate, I just need to be able to take some user input (namely a dropdown list populated by MANUFACTURER values) that will generate a table of those records. Ideally each row will have a clickable link in the last column which will be the static URL + SKU.

Thanks for all your efforts thus far.

-Tortuga
(Brian)


<?php			
 
require 'db.php';
 
				function displayItems($result)
				{
				
				 				 print "<h1>Our Catalog</h1>\n";
								 
								 print "\n<table border=1 align=left>\n<tr>\n" .
								 			 "\n\t<td>ID</td>" .
											 "\n\t<td>CATALOG</td>" .
											 "\n\t<td>DESCRIPTION</td>" .
											 "\n\t<td>MANUFACTURER</td" .
											 "\n\t<td>UPC_CODE</td>" .
											 "\n</tr>";
											 
								 while ($row = @ mysql_fetch_row($result))
								 {
								 			 // ... start a TABLE row ...
											 print "\n<tr>";
											 // ... print out each of the fields in that row as a separate TD
											 foreach($row as $data)
											 							print"\n\t<td align=left> {$data} </td>";
																		
											 // Finish the row
											 print "\n</tr>\n";
								 }
								 
								 //Then finish the table
								 print "\n</table>\n";
			 }
				
			 $query = "SELECT * FROM items";
								 
			if (!($connection = @ mysql_connect("localhost","user","PW")))
				 die("Could not connect");
			
			if (!(mysql_select_db($databaseName, $connection)))
				 showerror();
			
			if (!($result = @ mysql_query ($query, $connection)))
				 showerror();
			
			displayItems($result);
			
			?>

Open in new window

0
 
LVL 109

Accepted Solution

by:
Ray Paseur earned 500 total points
ID: 24435663
Sorry I do not have much time today to work on this.  Please look this over and see if it looks like what you want.  I cannot test it, and I am not sure about the HTML for the "select" without looking it up, but I think the logic is OK.

Best regards, ~Ray
<?php // RAY_temp_tortuga.php
// DB CONNECTIVITY
require 'db.php';
if (!($connection = mysql_connect("localhost","user","PW")))
{
    die("Could not connect");
}
if (!(mysql_select_db($databaseName, $connection)))
{
    showerror(); 
    die("Could not Select");
}
 
 
 
// IF A MANUFACTURER IS NOT SELECTED
if (empty($_POST))
{
   $sql = "SELECT DISTINCT MANUFACTURER FROM items ORDER BY MANUFACTURER";
   $res = mysql_query($sql);
   if (!$res) die( mysql_error() );
   echo "<form method=\"post\">\n";
   echo "CHOOSE MANUFACTURER<br/>\n"
   while ($row = mysql_fetch_assoc($res))
   {
       echo "<select name=\"m\" value=\"{$row["MANUFACTURER"]}\">$row["MANUFACTURER"]</select>\n";
   }
   echo "<input type=\"submit\">\n";
   echo "</form>\n";
   die();
}
 
 
 
// IF A MANUFACTURER IS SELECTED
$m = mysql_real_escape_string($_POST["m"]);
// CONSTRUCT A QUERY
$query = "SELECT * FROM items WHERE MANUFACTURER = $m";
// RUN THE QUERY
if (!($result = mysql_query ($query, $connection))) 
{
    showerror(); 
    die("Query Error");
}
 
// DISPLAY THE ITEMS
displayItems($result);
 
 
 
// FUNCTION TO DISPLAY A TABLE FROM THE DB RESULTS SET
function displayItems($result)
{
    print "<h1>Our Catalog</h1>\n";
    print "\n<table border=1 align=left>\n<tr>\n" .
       "\n\t<td>ID</td>" .
       "\n\t<td>CATALOG</td>" .
       "\n\t<td>DESCRIPTION</td>" .
       "\n\t<td>MANUFACTURER</td" .
       "\n\t<td>UPC_CODE</td>" .
       "\n\t<td>SKU</td>" .
       "\n</tr>";
    while ($row = mysql_fetch_assoc($result))
    {
// INJECT LOCAL VARIABLES INTO OUR NAMESPACE     
        extract($row);
// PUT YOUR CHOSEN URL INTO THIS LINE:         
        $sku_link = '<a href="http://www.url.com?sku=' . $SKU . '">' . $sku . '</a>';
        print "\n<tr>";
        print"\n\t<td align=left> {$ID} </td>";
        print"\n\t<td align=left> {$CATALOG} </td>";
        print"\n\t<td align=left> {$DESCRIPTION} </td>";
        print"\n\t<td align=left> {$MANUFACTURER} </td>";
        print"\n\t<td align=left> {$UPC_CODE} </td>";
        print"\n\t<td align=left> {$sku_link} </td>";
         
        print "\n</tr>\n";
    }
    print "\n</table>\n";
}

Open in new window

0
 

Author Comment

by:Tortuga
ID: 24437546
Awesome! I got it up and running, and it threw an error on line 34:

echo "<select name=\"m\" value=\"{$row["MANUFACTURER"]}\">$row["MANUFACTURER"]</select>\n";

Error is: Parse error: syntax error, unexpected '"', expecting T_STRING or T_VARIABLE or T_NUM_STRING in /home/c1bigfam/public_html/new/test_mama.php on line 34

This means there is an extra ", or maybe there needs to be an ending " ?

Thanks again, we're getting there!
0
 

Author Comment

by:Tortuga
ID: 24442451
This page is generated: http://www.1bigfamily.info/new/test_load.php

If I remove the quotes from MANUFACTURER in :
echo "<select name=\"m\" value=\"{$row["MANUFACTURER"]}\">$row["MANUFACTURER"]</select>\n";
0
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 24442897
Right, as I said, "I cannot test it, and I am not sure about the HTML for the "select" without looking it up"

Looks like that is supposed to be more like described here:
http://htmlhelp.com/reference/html40/forms/option.html

So the form generation might look more like this...

   echo "<form method=\"post\">\n";
   echo "CHOOSE MANUFACTURER<br/>\n"
   echo "<select name=\"m\">\n";
   while ($row = mysql_fetch_assoc($res))
   {
       echo "<option>$row['MANUFACTURER']</option>\n";
   }
   echo "</select>\n";
   echo "<input type=\"submit\">\n";
   echo "</form>\n";

Open in new window

0
 

Author Comment

by:Tortuga
ID: 24443027
Parse error: syntax error, unexpected T_ENCAPSED_AND_WHITESPACE, expecting T_STRING or T_VARIABLE or T_NUM_STRING

error on line 35

Seems like the same line (part of logic) causing the error.
33 while ($row = mysql_fetch_assoc($res))
34   {
35       echo "<option>$row['MANUFACTURER']</option>\n";
36   }

Open in new window

0
 

Author Comment

by:Tortuga
ID: 24443033
I'll look at the link you posted to try to troubleshoot.
Thanks
0
 
LVL 109

Assisted Solution

by:Ray Paseur
Ray Paseur earned 500 total points
ID: 24443058
Yeah, those apostrophes are a nuisance with echo.  Try this instead
   echo "<form method=\"post\">\n";
   echo "CHOOSE MANUFACTURER<br/>\n"
   echo "<select name=\"m\">\n";
   while ($row = mysql_fetch_assoc($res))
   {
       $m = $row["MANUFACTURER"];
       echo "<option>$m</option>\n";
   }
   echo "</select>\n";
   echo "<input type=\"submit\">\n";
   echo "</form>\n";

Open in new window

0
 

Author Comment

by:Tortuga
ID: 24443098
0
 
LVL 109

Assisted Solution

by:Ray Paseur
Ray Paseur earned 500 total points
ID: 24443116
Try escaping quotes in the query like this...
$query = "SELECT * FROM items WHERE MANUFACTURER = \"$m\"";

Open in new window

0
 

Author Comment

by:Tortuga
ID: 24443172
nice, that works well.!I think there is just one more aspect of this to get wrking. The search box for user input. User select a radio or checkbox to search by catalog or UPC, then enters their search params in a text box.
We can use the same submit button right?

Thanks much, Ray, it is looking great!

When this is wrapped up, extra points definitely!
0
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 24443197
@Tortuga: Why not close this question out and ask another one?  

For one thing, I do not understand exactly what you're lookng for at this point.  For another, in spite of your generosity, you can't give more than 500 points per question, and you might find that other experts could help with the rest of these issues.  

Best regards, ~Ray
0
 

Author Closing Comment

by:Tortuga
ID: 31582051
Thanks Ray, I appreciate all of your efforts.
0
 

Author Comment

by:Tortuga
ID: 24443286
Very well.
Thank you Ray.
0
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 24443658
Thanks for the points.  I'll be on the lookout for your next Q!

best, ~Ray
0

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Author Note: Since this E-E article was originally written, years ago, formal testing has come into common use in the world of PHP.  PHPUnit (http://en.wikipedia.org/wiki/PHPUnit) and similar technologies have enjoyed wide adoption, making it possib…
This article discusses four methods for overlaying images in a container on a web page
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to count occurrences of each item in an array.

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question