Help with formating and displaying PHP and MYSQL search results.

Question

Motorider 0 Light Poster

15 Years Ago

If this should be posted in another area, such as, layout and design please let me know. A person who post's as Ardav on this board has gotten me this far.

I have a relational database that pulls by brand and descriptive keyword. I wish to search and display on the same page. Allowing people to search again without using the back or a link button. Displaying the search form, and related text contained to left half of the page and the search results displayed in a column format on the right half of the page.

My search page code is;

<html> 
    
<head> 
        
<title>Bus Parts Search</title> 
    <link href="test.css" rel="stylesheet" type="text/css" />
    <style type="text/css">
<!--
.style1 {font-size: 12pt}
.style2 {font-size: 12px}
-->
    </style>
</head> 
 
<body>
<div>
<div class="banner"><img src="graphics/stainlessbg2copy.jpg" width="950" height="125"></div>
    
<div class="cont"></div>

<div class="head1">
  <h4><u>Search Parts Here</u></h4>
  <h4 class="style1">First Select type. </h4>
  <h4 class="style1">Enter part description keyword<br>
  and submit.</h4>
  <h4 class="style1">To see a complete list of parts<br>
    on hand for a type.<br>
    Submit with description blank.</h4>
  </div>

<div class="search">  
	<form action="results2.php" method="post"> 
    
	<label for="mytype">Type:</label>
	<select name="mytype" id="mytype">
    <option value="1">GM</option>
    <option value="2">MCI</option>
    <option value="3">Engines</option>
	</select>

	<label for="desc">
<br>
<br>
	Description:</label>
	<input type="text" name="desc" id="desc" />
    <input type="submit" name="submit" value="Submit" /> 
	</form>
</div>


<div class="results">Content for  class "results" Goes Here</div>
</div>
</body> 
</html>

My PHP code is;

<?php
//Connect To Database
$hostname='';
$username='';
$password='';
$dbname='';


mysql_connect($hostname,$username, $password) OR DIE ('Unable to connect to database! Please try again later.');
mysql_select_db($dbname);



$mytype = $_POST['mytype']; //this is an integer
$term = $_POST['desc']; //this is a string



$sql = mysql_query("select * FROM products WHERE description like '%$term%' AND product_type_id = '{$mytype}'");

while ($row = mysql_fetch_array($sql))
{
	echo 'ID: '.$row['ID'];
	echo '<br/> Part No: '.$row['part no'];
	echo '<br/> Description: '.$row['description'];
	echo '<br/> Price: '.$row['price'];
	echo '<br/><br/>';
	}

?>

Responses will be grealtly appreciated.

mysql php

4 Contributors
8 Replies
478 Views
11 Months Discussion Span
Latest Post 14 Years Ago Latest Post by dschuett

slyme 27 Junior Poster in Training

15 Years Ago

Stick the PHP code right at the top of the page (just to keep things neat and tidy really) above the HTML stuff.

Create an empty variable, say $output_result before your existing code.

Now, surround your code (not the empty variable) with an if statement. The if statement should test whether or not the Submit button has been clicked.

Instead of using echo in your code use the empty variable and fill it with the result.

Finally, echo the empty (or not!) variable in the div where you want to display the results.

Remember to set the form action to refer back to the same page.

hope this helps ...
Simon

slyme 27 Junior Poster in Training

15 Years Ago

Ardav is correct Motorider, you do not have a variable function so that's not the problem.

Here's what I would do at this point:

Using phpMyAdmin or some MySQL tool I would simply run the select query and check for results. If that doesn't help, I would then replace all of the code inside the if statement with:

$search_result = 'form submitted';

Then I would submit the form just to check that side of it is working (which it should be).

One of these tests should highlight the problem. Perhaps you could add an if statement inside the database query to return 'no records found' if the search result is genuinely empty.

Let us know how it goes ...

Simon.

Motorider commented: He helped me out tremendously +1

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

Motorider 0 Light Poster · Answer 1 · 2009-08-26T09:00:21+00:00

Simon, Thanks for your response. I can see or feel the direction your going. And I studied the information in your post today, but I don't yet have the knowledge to put it together.

I know its a lot to ask, but could you give me some code to play with?

I know about the form action <form method="post" action="<?php echo $_SERVER['PHP_SELF'];"> I need more help with the variable, using this variable, echoing the variable and displaying the results. A div is already setup to contain the results. The if statement as well.

Thanks

slyme 27 Junior Poster in Training · Answer 2 · 2009-08-26T14:32:50+00:00

Here you go Motorider - I have set the form action empty, this has the same effect as your code above.

I cannot check if the code runs properly once the form button is clicked because, obviously, I don't have the database.

<?php
	
//Create an empty variable
$search_result = '';

//If statement, to check if the submit button has been clicked.
if (isset($_POST['submit'])) {
	//Connect To Database
	$hostname='';
	$username='';
	$password='';
	$dbname='';

	mysql_connect($hostname,$username, $password) OR DIE ('Unable to connect to database! Please try again later.');
	mysql_select_db($dbname);
	
	$mytype = $_POST['mytype']; //this is an integer
	$term = $_POST['desc']; //this is a string
	
	$sql = mysql_query("select * FROM products WHERE description like '%$term%' AND product_type_id = '{$mytype}'");
	
	while ($row = mysql_fetch_array($sql)) {
		// Use the variable to build the result
		$search_result .= 'ID: '.$row['ID'] . "\n";
		$search_result .=  '<br/> Part No: '.$row['part no'] . "\n";
		$search_result .=  '<br/> Description: '.$row['description'] . "\n";
		$search_result .=  '<br/> Price: '.$row['price'] . "\n";
		$search_result .=  '<br/><br/>' . "\n";
	}
}
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Bus Parts Search</title> 
<link href="test.css" rel="stylesheet" type="text/css" />
<style type="text/css">
<!--
.style1 {font-size: 12pt}
.style2 {font-size: 12px}
-->
</style>
</head>

<body>
<div>
<div class="banner"><img src="graphics/stainlessbg2copy.jpg" width="950" height="125"></div>
    
<div class="cont"></div>

<div class="head1">
  <h4><u>Search Parts Here</u></h4>
  <h4 class="style1">First Select type. </h4>
  <h4 class="style1">Enter part description keyword<br>
  and submit.</h4>
  <h4 class="style1">To see a complete list of parts<br>
    on hand for a type.<br>
    Submit with description blank.</h4>
  </div>

<div class="search">  
	<form action="" method="post"> 
    
	<label for="mytype">Type:</label>
	<select name="mytype" id="mytype">
    <option value="1">GM</option>
    <option value="2">MCI</option>
    <option value="3">Engines</option>
	</select>

	<label for="desc">
<br>
<br>
	Description:</label>
	<input type="text" name="desc" id="desc" />
    <input type="submit" name="submit" value="Submit" /> 
	</form>
</div>


<div class="results"><?php echo $search_result ?></div>
</div>

</body>
</html>

Motorider 0 Light Poster · Answer 3 · 2009-08-27T08:19:01+00:00

Simon, again, I thank you for your help. I am new to php and this is my first project.

The script seems to be trying to work (no errors) but doesn't display the results. I have been playing with it all day but with my limited knowledge I can't make it show the results.

On php.net it says that since echo is a language construct that it can't be used with variable functions. Is this why the results don't display?

diafol · Answer 4 · 2009-08-27T12:33:02+00:00

You don't have a variable function. The php looks ok to me except you need a semicolon after the variable name in the div. If this doesn't sort it. Try to echo your query and your $search_result evry time it is changed.

Motorider 0 Light Poster · Answer 5 · 2009-09-06T07:09:53+00:00

Alright finally solved the riddle after maddening hours of trying everything I could think of. Simplified it down to no formatting just a form, code, and echo. And it wouldn't work. It was then that I thought to myself what would happen if I saved it with a .php extension. Wallah it all came together. In the dozen or so tutorials I have read, I don't remember it being a clear point. The simplified code is below.

Thank you very much for your help!

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
 
<body> 
<form method="POST" action="<?php echo $_SERVER['PHP_SELF']; ?>">
 
    
	<label for="mytype">Type:</label>
	<select name="mytype" id="mytype">
    <option value="1">GM</option>
    <option value="2">MCI</option>
    <option value="3">Engines</option>
	</select>

	<label for="desc">
<br/>
<br/>
	Description:</label>
	<input type="text" name="desc" id="desc" />
    <input type="submit" name="submit" value="Submit" /> 
	</form>

</body> 
</html>


<?php

//Create an empty variable
$search_result = '';

//If statement, to check if the submit button has been clicked.
if (isset($_POST['submit'])) {

//Connect To Database

include "config.php"; //config.php contains login information for mysql_connect

mysql_connect($hostname,$username,$password) OR DIE ('Unable to connect to database! Please try again later.');
mysql_select_db($dbname);


$mytype = $_POST['mytype']; //this is an integer
$term = $_POST['desc']; //this is a string
	
$sql = mysql_query("select * FROM products WHERE description like '%$term%' AND product_type_id = '{$mytype}'");
	
while ($row = mysql_fetch_array($sql)) {
	// Use the variable to build and format the result
	$search_result .=  '<br/> Part No: '.$row['part no'] . "\n";
	$search_result .=  '<br/> Description: '.$row['description'] . "\n";
	$search_result .=  '<br/> Price: '.$row['price'] . "\n";
	$search_result .=  '<br/><br/>' . "\n";
	}
}

echo $search_result; //display the results

?>

dschuett -1 Junior Poster · Answer 6 · 2010-08-11T20:53:07+00:00

Hey GREAT code! It was exactly what I was looking for. I am just stumped on one thing. Since the results are displayed within the same page, is there a way to clear each search result when you do a new search. Right now you can do multiple searches, but it keeps the previous search results listed and just adds the next search right underneath.

Here is my code:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">

 <html>
            <head>
                <title>Search the Database</title>
            </head>
            <body>
            <form method="POST" action="<?php echo $_SERVER['PHP_SELF']; ?>">
             Search: <select name="type">
                     <option value='po_num' selected>PO Number</option>
                     <option value='date'>Date</option>
                     <option value='vin_num'>VIN Number</option>
                     </select>
for: <input type="text" name="term" /><br />
            <input type="submit" name="submit" value="submit" />
            </form>
</body>
</html>
    <?php
        $search_result = '';
        if (isset($_POST['submit'])) {
        mysql_connect ("localhost", "myuser","mypass")  or die (mysql_error());
        mysql_select_db ("inventory");
        $type = $_POST['type'];
        $term = $_POST['term'];
        $safetype = mysql_real_escape_string($_POST['type']);
        $safeterm = mysql_real_escape_string($_POST['term']);
        $sql = mysql_query("select * from partsorder where $type like '%$term%'");
        while ($row = mysql_fetch_array($sql)){
            $search_result .= '<br/> <B>PO Number:</B> '.$row['po_num'] . "\n";
            $search_result .= '<br/><B> VIN Number:</B> '.$row['vin_num'] . "\n";
            $search_result .= '<br/> <B>Description:</B> '.$row['para'] . "\n";
            $search_result .= '<br/> <B>Purchase Agent:</B> '.$row['purch_agt'] . "\n";
            $search_result .= '<br/><br/>' . "\n";
            }
}

echo $search_result;
        ?>