Solved

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

Posted on 2009-05-15
20
240 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 108

Expert Comment

by:Ray Paseur
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:Tortuga
Comment Utility
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 108

Expert Comment

by:Ray Paseur
Comment Utility
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
Comment Utility
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 108

Accepted Solution

by:
Ray Paseur earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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 108

Expert Comment

by:Ray Paseur
Comment Utility
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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

Author Comment

by:Tortuga
Comment Utility
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
Comment Utility
I'll look at the link you posted to try to troubleshoot.
Thanks
0
 
LVL 108

Assisted Solution

by:Ray Paseur
Ray Paseur earned 500 total points
Comment Utility
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
Comment Utility
0
 
LVL 108

Assisted Solution

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

Open in new window

0
 

Author Comment

by:Tortuga
Comment Utility
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 108

Expert Comment

by:Ray Paseur
Comment Utility
@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
Comment Utility
Thanks Ray, I appreciate all of your efforts.
0
 

Author Comment

by:Tortuga
Comment Utility
Very well.
Thank you Ray.
0
 
LVL 108

Expert Comment

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

best, ~Ray
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduction HTML checkboxes provide the perfect way for a web developer to receive client input when the client's options might be none, one or many.  But the PHP code for processing the checkboxes can be confusing at first.  What if a checkbox is…
Consider the following scenario: You are working on a website and make something great - something that lets the server work with information submitted by your users. This could be anything, from a simple guestbook to a e-Money solution. But what…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

772 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now