Trying to get a simple PHP SQL search to work

Disead · August 14, 2014

Hey all,

I need your help!

So, I am the web designer for a small company, but I use that term loosely as I for the most part am limited to design through Dreamweaver and Muse. I know a decent amount of HTML off the top of mey head, but when it comes to PHP, yikes...

My company asked me to make a simple searchable web page based on their price guide/catalog, built into their website. I have MySQL set up within their godaddy. I can use the SQL search functions to get the exact results back that I need, and the SQL cPanel gives me the following code:

SELECT * FROM `SMQSQL` WHERE `Scott` = '(Whatever I search for)' ORDER BY `LINEID` ASC

This makes sense to me up to here; this is very simple language, However, I have been pulling my hair out for days trying to get a simple search function as an actual PHP *PAGE* going.

Basically, I need to set up a PHP form search (not hard) that will go back and log in to my SQL database named SMQ (not too bad yet), perform a search out of a specific column named Scott (getting a little harder, and somewhat lost), take the search results from specific columns (getting harder), and display them in a neat little table (now I'm lost). My SQL database is set up with the following columns:

LINEID, Scott, Den, Color, Cond, 70, 70J, 75, 75J, 80, 80J, 85, 85J, 90, 90J, 95, 95J, 98, 98J, 100

Where LINEID is the index. LINEID is set to INT (5 char), the next 4 are TEXT (255 char, item descriptors), and the rest INT (9 char; prices). When the results are displayed, I would like all but the LINEID visible.

I have tried and tried and tried playing with my PHP coding but am ready to shoot my computer. Here it is:

<!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>Online Search</title>
</head>
<body>
<h3>Search Online</h3>
<p>You may search by Catalog number. Please use the exact Catalog number or you may receive an error. Please note that if searching for more than a basic item, include the full Catalog number WITHOUT spaces.</p>

<form  method="post" action="search.php?go"  id="searchform">
<input  type="text" name="Scott">
<input  type="submit" name="submit" value="Search">
</form>

<?php
if(isset($_POST['submit'])){
if(isset($_GET['go'])){
$Scott=$_POST['Scott'];;
 
  $db=mysql_connect  ("localhost", "guestuser",  "guestuser") or die ('Error connecting to the database. Error: ' . mysql_error());
      
      $mydb=mysql_select_db("SMQ");
 
      $sql="SELECT  Scott, Den, Color, Cond, 70, 70J, 75, 75J, 80, 80J, 85, 85J, 90, 90J, 95, 95J, P98, 98J, 100 FROM SMQ WHERE Scott LIKE '%" . $Scott .  "%';
 
  $result=mysql_query($sql);
 
  while($row=mysql_fetch_array($result)){
              $Scott=$row['Scott'];
              $Den=$row['Den'];
              $Color=$row['Color'];
              $Cond=$row['Cond'];
              $70=$row['70'];
              $70J=$row['70J'];
              $75=$row['75'];
              $75J=$row['75J'];
              $80=$row['80'];
              $80J=$row['80J'];
              $85=$row['85'];
              $85J=$row['85J'];
              $90=$row['90'];
              $90J=$row['90J'];
              $95=$row['95'];
              $95J=$row['95J'];
              $98=$row['98'];
              $98J=$row['98J'];
              $100=$row['100'];
    
      echo "<ul>\n";
  echo "<li>" . "<a  href=\"SMQ.php?id=$Scott\">"   .$Scott . " " . $Den .  " " . $Color .  " " . $Cond .  " " . $70 .  " " . $70J .  " " . $75 .  " " . $75J .  " " . $80 .  " " . $80J .  " " . $85 .  " " . $85J .  " " . $90 .  " " . $90J .  " " . $95 .  " " . $95J .  " " . $98 .  " " . $98J .  " " . $100 .  "</a></li>\n";
  echo "</ul>";
  }
        }
      else{
      echo  "<p>Please enter a search query</p>";
  }   
      ?>

I know it's probably pretty bad, but hopefully at least you can get an idea of what I'm trying to accomplish. Please dear God tell me what I am doing wrong! I'm sure it will take a knowledgeable user 5 minutes to fix this, but you would be saving my skin!

THANKS!!!

Edited August 14, 2014 by mac_gyver
removed link to op's cp and code tags please when posting code

paddy_fields · August 14, 2014

Hi

Firstly mysql_query is deprecated, so use a newer method like mysqli

As this is just a search form and their doesn't appear to be any password data etc being queried i'd suggest using GET instead of POST for this (uses the URL instead)

Here's something to get you started... it's untested but should give you an idea of how it works

the form

<form name="searchForm" method="GET" action="#">
  <input  type="text" name="scott">
  <input  type="submit" name="submit" value="Search">
</form>

Put your processing PHP before the HTML, as it will need to execute first and then display the data within the HTML.

To get the results (using prepared statements)

// $db is the database connection, which you'll need to read up on
// note: you need to validate the $_GET['scott'], this is just an example so i've left that out

$validScott = $_GET['scott'];

$stmt = $db->prepare('SELECT Scott, Den, Color FROM SMQ WHERE Scott = ?');
$stmt->bind_param('s',$validScott]);
$stmt->execute;
$result = $stmt->get_result();
$stmt->close();

Within the body of HTML


<table>
  <tr>
     <td>Scott</td>
     <td>Den</td>
     <td>Color</td>
  </tr>
  <tr>

  <?php
  while($row = $result->fetch_assoc()){ ?>
    <td><?php echo $row['Scott']?></td>
    <td><?php echo $row['Den']?></td>
    <td><?php echo $row['Color']?></td>

  <?php  } ?>

  </tr>
</table>

Edited August 14, 2014 by paddy_fields

paddy_fields · August 14, 2014

Actually, the table I've made is wrong, as the <tr> should be in the loop.

But regardless you could also build the HTML immediately after the query like this:

while($row = $result->fetch_accos()){

   $queryResult.= "
   <tr>
     <td>$row[Scott]</td>
     <td>$row[Den]</td>
     <td>$row[Color]</td>
   </tr>
   ";

}

And then in the HMTL just echo out the $queryResult

<table>
    <tr>
        <td>Scott</td>
        <td>Den</td>
        <td>Color</td>
     <tr>
     <?php echo $queryResult; ?>
</table>

Edited August 14, 2014 by paddy_fields

Disead · August 14, 2014

Thank you paddy_fields, I will try out your suggestions and come back with (hopefully) positive results!

Disead · August 15, 2014

OK, here it goes.

I made the changes you suggested below as you will see in the Code. I went ahead and saved it as SMQ.php and uploaded it to my test site.

If I remember correctly, I should just be able to go to www.(mytestsite).com/SMQ.php and have the php section work when accessed directly through say Firefox, correct?

On a slight side note, I have done this months ago in the past, and it has worked, but I was working with sort of a "kid version" of SQL, referencing my .php to a google doc instead of a SQL server, but that was limited in ability, thus the switch to SQL. However, with that setup, I COULD go to www.(mytestsite).com/SMQ.php and it would actually come up just fine.

So, the problem now is this. I upload my SMQ.php, same as I did when it worked with google docs, same location. It shows in my File Manager that the file is in fact there and exists. I can download it from the File Manager and get the exact file that I had uploaded, the one you helped me with. However, if I try to go directly to www.(mytestsite).com/SMQ.php, nothing happens, as in, nothing at all. I do not get a 404 error saying that nothing is there, I just get a blank white screen. When I go to look at the page source, instead of the code that should be there, I get a blank page.

Is it something I did wrong in writing the php file? Its below, take a look and let me know what you think please....

<!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>Online SMQ</title>
</head>

<?php
$mysqli = new mysqli("localhost", "guestuser", "guestuserpassword1", "SMQ");
if ($mysqli->connect_errno) {
    echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
}

?>

<form name="searchForm" method="GET" action="#">
  <input  type="text" name="scott">
  <input  type="submit" name="submit" value="Search">
</form>

<?php
$validScott = $_GET['scott'];

$stmt = $db->prepare('SELECT Scott, Den, Color, Cond, 70, 70J, 75, 75J, 80, 80J, 85, 85J, 90, 90J, 95, 95J, P98, 98J, 100 FROM SMQ WHERE Scott = ?');
$stmt->bind_param('s',$validScott]);
$stmt->execute;
$result = $stmt->get_result();
$stmt->close(); 

while($row = $result->fetch_accos()){

   $queryResult.= "
   <tr>
     <td>$row[Scott]</td>
     <td>$row[Den]</td>
     <td>$row[Color]</td>
	 <td>$row[Cond]</td>
	 <td>$row[70]</td>
	 <td>$row[70J]</td>
	 <td>$row[75]</td>
	 <td>$row[75J]</td>
	 <td>$row[80]</td>
	 <td>$row[80J]</td>
	 <td>$row[85]</td>
	 <td>$row[85J]</td>
	 <td>$row[90]</td>
	 <td>$row[90J]</td>
	 <td>$row[95]</td>
	 <td>$row[95J]</td>
	 <td>$row[98]</td>
	 <td>$row[98J]</td>
	 <td>$row[100]</td>
	 
   </tr>
   ";

}

?>

<table>
    <tr>
        <td>Scott</td>
        <td>Den</td>
        <td>Color</td>
        <td>Cond</td>
	    <td>70</td>
	    <td>70J</td>
	    <td>75</td>
        <td>75J</td>
	    <td>80</td>
	    <td>80J</td>
	    <td>85</td>
	    <td>85J</td>
	    <td>90</td>
	    <td>90J</td>
	    <td>95</td>
	    <td>95J</td>
	    <td>98</td>
	    <td>98J</td>
	    <td>100</td>
     <tr>
     <?php echo $queryResult; ?>
</table>

Dreamweaver is reporting a syntax error on lines 25 which is

$stmt->bind_param('s',$validScott]);

and line 39 which is

<td>$row[70J]</td>

THANK YOU!!!

CroNiX · August 15, 2014

You are most likely getting a blank page because you have display_errors turned off, and there is a fatal error. Turn it on and check your error logs in the meantime.

What is this? accos?

while($row = $result->fetch_accos()){

Why are you not putting quotes around the keys, since they're strings, in your associative array?

<td>$row['Scott']</td>

paddy_fields · August 15, 2014

Sorry, they were both typo's on my behalf ::)

while($row = $result->fetch_assoc()){

As CroNiX says you need to turn your error reporting on to see what the problem is... from what I can see you have a stray closing bracket in your bind_param()

$stmt->bind_param('s',$validScott]);

Disead · August 15, 2014

Alright, I made the recommended changes. Still getting a blank screen. Error reporting says:

TypeError: can't access dead object jquery-1.10.2.min.js:6
TypeError: can't access dead object jquery-1.10.2.min.js:6
TypeError: can't access dead object jquery-1.10.2.min.js:6
The character encoding of the HTML document was not declared. The document will render with garbled text in some browser configurations if the document contains characters from outside the US-ASCII range. The character encoding of the page must be declared in the document or in the transfer protocol. SMQ.php
TypeError: can't access dead object jquery-1.10.2.min.js:6
TypeError: can't access dead object jquery-1.10.2.min.js:6
TypeError: can't access dead object jquery-1.10.2.min.js:6

This is the edited php I used:

<!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>Online SMQ</title>
</head>

<?php
$mysqli = new mysqli("localhost", "guestuser", "guestuser", "SMQ");
if ($mysqli->connect_errno) {
    echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
}

?>

<form name="searchForm" method="GET" action="#">
  <input  type="text" name="scott">
  <input  type="submit" name="submit" value="Search">
</form>

<?php
$validScott = $_GET['scott'];

$stmt = $db->prepare('SELECT Scott, Den, Color, Cond, 70, 70J, 75, 75J, 80, 80J, 85, 85J, 90, 90J, 95, 95J, P98, 98J, 100 FROM SMQ WHERE Scott = ?');
$stmt->bind_param('s',$validScott);
$stmt->execute;
$result = $stmt->get_result();
$stmt->close(); 

while($row = $result->fetch_assoc()){

   $queryResult.= "
   <tr>
     <td>$row[Scott]</td>
     <td>$row[Den]</td>
     <td>$row[Color]</td>
	 <td>$row[Cond]</td>
	 <td>$row[70]</td>
	 <td>$row[70J]</td>
	 <td>$row[75]</td>
	 <td>$row[75J]</td>
	 <td>$row[80]</td>
	 <td>$row[80J]</td>
	 <td>$row[85]</td>
	 <td>$row[85J]</td>
	 <td>$row[90]</td>
	 <td>$row[90J]</td>
	 <td>$row[95]</td>
	 <td>$row[95J]</td>
	 <td>$row[98]</td>
	 <td>$row[98J]</td>
	 <td>$row[100]</td>
	 
   </tr>
   ";

}

?>

<table>
    <tr>
        <td>Scott</td>
        <td>Den</td>
        <td>Color</td>
        <td>Cond</td>
	    <td>70</td>
	    <td>70J</td>
	    <td>75</td>
        <td>75J</td>
	    <td>80</td>
	    <td>80J</td>
	    <td>85</td>
	    <td>85J</td>
	    <td>90</td>
	    <td>90J</td>
	    <td>95</td>
	    <td>95J</td>
	    <td>98</td>
	    <td>98J</td>
	    <td>100</td>
     <tr>
     <?php echo $queryResult; ?>
</table>

Dreamweaver is still saying there is a syntax error in line 39:

 <td>$row[70J]</td>

Edited August 15, 2014 by Disead

Disead · August 15, 2014

And I'm confused by the error

The character encoding of the HTML document was not declared. The document will render with garbled text in some browser configurations if the document contains characters from outside the US-ASCII range. The character encoding of the page must be declared in the document or in the transfer protocol. SMQ.php

because isn't that what this:

<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8"/>

<title>Online SMQ</title>
</head>

covers?

Disead · August 15, 2014

I went ahead and reordered a couple items and added a couple short lines, hoping it would fix it. It now looks like this:

<!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>Online SMQ</title>
</head>
<body>

<form name="searchForm" method="GET" action="#">
  <input  type="text" name="scott">
  <input  type="submit" name="submit" value="Search">
</form>
<p>
<?php

$mysqli = new mysqli("localhost", "guestuser", "guestuser", "SMQ");
if ($mysqli->connect_errno) {
    echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
}

$validScott = $_GET['scott'];

$stmt = $db->prepare('SELECT Scott, Den, Color, Cond, 70, 70J, 75, 75J, 80, 80J, 85, 85J, 90, 90J, 95, 95J, P98, 98J, 100 FROM SMQ WHERE Scott = ?');
$stmt->bind_param('s',$validScott);
$stmt->execute;
$result = $stmt->get_result();
$stmt->close(); 

while($row = $result->fetch_assoc()){

   $queryResult.= "
   <tr>
     <td>$row[Scott]</td>
     <td>$row[Den]</td>
     <td>$row[Color]</td>
	 <td>$row[Cond]</td>
	 <td>$row[70]</td>
	 <td>$row[70J]</td>
	 <td>$row[75]</td>
	 <td>$row[75J]</td>
	 <td>$row[80]</td>
	 <td>$row[80J]</td>
	 <td>$row[85]</td>
	 <td>$row[85J]</td>
	 <td>$row[90]</td>
	 <td>$row[90J]</td>
	 <td>$row[95]</td>
	 <td>$row[95J]</td>
	 <td>$row[98]</td>
	 <td>$row[98J]</td>
	 <td>$row[100]</td>
	 
   </tr>
   ";

}

?>

<table>
    <tr>
        <td>Scott</td>
        <td>Den</td>
        <td>Color</td>
        <td>Cond</td>
	    <td>70</td>
	    <td>70J</td>
	    <td>75</td>
        <td>75J</td>
	    <td>80</td>
	    <td>80J</td>
	    <td>85</td>
	    <td>85J</td>
	    <td>90</td>
	    <td>90J</td>
	    <td>95</td>
	    <td>95J</td>
	    <td>98</td>
	    <td>98J</td>
	    <td>100</td>
     <tr>
     <?php echo $queryResult; ?>
</table>
</body>
</html>

This swapped the order of the form and sql connect, and added lines 7, 13, 84, and 85, as well as removed the duplicate <?php and ?> that were left over from the previous order, all in an attempt to cross my t's and dot my i's so to speak.

Still getting the "The character encoding of the HTML document was not declared. The document will render with garbled text in some browser configurations if the document contains characters from outside the US-ASCII range. The character encoding of the page must be declared in the document or in the transfer protocol." Error, as well as Dreamweaver saying there is a syntax error in line 38.

???

jazzman1 · August 15, 2014

Use the next pattern when indexes of the array consists a string:

<td>$row[70J]</td>

to

<td>{$row['70J']}</td>

Why did you close the statement before fetching all rows from db server?

The character encoding of the HTML document was not declared.

Where do you get this message in the firebird (js tool) console?

paddy_fields · August 15, 2014

Why did you close the statement before fetching all rows from db server?

The rows have been fetched and stored in $result?

jazzman1 · August 15, 2014

The rows have been fetched and stored in $result?

I missed that one

Before to concat (".") a string it's a good practice to define that variable to empty string otherwise you will get a notice of undefined variable!

$queryResult = '';

while($row = $result->fetch_assoc()){
 
$queryResult .= "
<tr>
<td>$row[Scott]</td>
<td>$row[Den]</td>
<td>$row[Color]</td>
<td>$row[Cond]</td>
etc ........
</tr>
";
 
}

echo $queryResult;

Edited August 15, 2014 by jazzman1

paddy_fields · August 15, 2014

Disead, you're making a connection called $mysqli, and then using $db....

$mysqli = new mysqli("localhost", "guestuser", "guestuser", "SMQ");
if ($mysqli->connect_errno) {
echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
}

Update your query to this....

$stmt = $mysqli->prepare

Also, is the table called 'SMQ', the database, or both? As you connect to a database called 'SMQ' and use it in your query also.

Edited August 15, 2014 by paddy_fields

Disead · August 15, 2014

1. Updated this

$queryResult.= "
   <tr>
     <td>$row[Scott]</td>
     <td>$row[Den]</td>
     <td>$row[Color]</td>
	 <td>$row[Cond]</td>
	 <td>$row[70]</td>
	 <td>$row[70J]</td>
	 <td>$row[75]</td>
	 <td>$row[75J]</td>
	 <td>$row[80]</td>
	 <td>$row[80J]</td>
	 <td>$row[85]</td>
	 <td>$row[85J]</td>
	 <td>$row[90]</td>
	 <td>$row[90J]</td>
	 <td>$row[95]</td>
	 <td>$row[95J]</td>
	 <td>$row[98]</td>
	 <td>$row[98J]</td>
	 <td>$row[100]</td>
	 
   </tr>

to this:

$queryResult.= "
   <tr>
     <td>$row['Scott']</td>
     <td>$row['Den']</td>
     <td>$row['Color']</td>
	 <td>$row['Cond']</td>
	 <td>$row['70']</td>
	 <td>$row['70J']</td>
	 <td>$row['75']</td>
	 <td>$row['75J']</td>
	 <td>$row['80']</td>
	 <td>$row['80J']</td>
	 <td>$row['85']</td>
	 <td>$row['85J']</td>
	 <td>$row['90']</td>
	 <td>$row['90J']</td>
	 <td>$row['95']</td>
	 <td>$row['95J']</td>
	 <td>$row['98']</td>
	 <td>$row['98J']</td>
	 <td>$row['100']</td>
	 
   </tr>

It looks like it moved the Dreamweaver syntax error to line 35 though.

2. Added line 29 here to define var as empty string first:

$stmt->close(); 

$queryResult = '';

while($row = $result->fetch_assoc()){

   $queryResult.= "

3. I fixed the $mysqli / $db discrepancy here:

$mysqli = new mysqli("localhost", "guestuser", "guestuser", "SMQ");
if ($mysqli->connect_errno) {
    echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
}

$validScott = $_GET['scott'];

$stmt = $mysqli->prepare('SELECT Scott, Den, Color, Cond, 70, 70J, 75, 75J, 80, 80J, 85, 85J, 90, 90J, 95, 95J, P98, 98J, 100 FROM SMQSQL WHERE Scott = ?');

4. I (believe) fixed the difference between the database/table name discrepancy as well, you were right in that I had mis-named the table; I believe the database name is SMQ and the table name is SMQSQL as seen here:

ScreenShot2014-08-15at91831AM_zps4969a63

So now in line 16 it refers to a database named SMQ vs. line 23 is now FROM SMQSQL:

$mysqli = new mysqli("localhost", "guestuser", "guestuser", "SMQ");
if ($mysqli->connect_errno) {
    echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
}

$validScott = $_GET['scott'];

$stmt = $mysqli->prepare('SELECT Scott, Den, Color, Cond, 70, 70J, 75, 75J, 80, 80J, 85, 85J, 90, 90J, 95, 95J, P98, 98J, 100 FROM SMQSQL WHERE Scott = ?');

So, I believe I have fixed everything that was caught this time. To answer the question of where the error reporting is coming from, its coming from the Browser Console within Firefox, as I have been trying to figure out why I am getting a blank page when trying to access /SMQ.php online. Of which, by the way, I am still getting the same error from the Console:

":The character encoding of the HTML document was not declared. The document will render with

garbled text in some browser configurations if the document contains characters from outside the

US-ASCII range. The character encoding of the page must be declared in the document or in the transfer protocol."

That is the error that pops up whenever I try to access the page at /SMQ.php.

The complete code is now:

<!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>Online SMQ</title>
</head>
<body>

<form name="searchForm" method="GET" action="#">
  <input  type="text" name="scott">
  <input  type="submit" name="submit" value="Search">
</form>
<p>
<?php

$mysqli = new mysqli("localhost", "guestuser", "guestuser", "SMQ");
if ($mysqli->connect_errno) {
    echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
}

$validScott = $_GET['scott'];

$stmt = $mysqli->prepare('SELECT Scott, Den, Color, Cond, 70, 70J, 75, 75J, 80, 80J, 85, 85J, 90, 90J, 95, 95J, P98, 98J, 100 FROM SMQSQL WHERE Scott = ?');
$stmt->bind_param('s',$validScott);
$stmt->execute;
$result = $stmt->get_result();
$stmt->close(); 

$queryResult = '';

while($row = $result->fetch_assoc()){

   $queryResult.= "
   <tr>
     <td>$row['Scott']</td>
     <td>$row['Den']</td>
     <td>$row['Color']</td>
	 <td>$row['Cond']</td>
	 <td>$row['70']</td>
	 <td>$row['70J']</td>
	 <td>$row['75']</td>
	 <td>$row['75J']</td>
	 <td>$row['80']</td>
	 <td>$row['80J']</td>
	 <td>$row['85']</td>
	 <td>$row['85J']</td>
	 <td>$row['90']</td>
	 <td>$row['90J']</td>
	 <td>$row['95']</td>
	 <td>$row['95J']</td>
	 <td>$row['98']</td>
	 <td>$row['98J']</td>
	 <td>$row['100']</td>
	 
   </tr>
   ";

}

?>

<table>
    <tr>
        <td>Scott</td>
        <td>Den</td>
        <td>Color</td>
        <td>Cond</td>
	    <td>70</td>
	    <td>70J</td>
	    <td>75</td>
        <td>75J</td>
	    <td>80</td>
	    <td>80J</td>
	    <td>85</td>
	    <td>85J</td>
	    <td>90</td>
	    <td>90J</td>
	    <td>95</td>
	    <td>95J</td>
	    <td>98</td>
	    <td>98J</td>
	    <td>100</td>
     <tr>
     <?php echo $queryResult; ?>
</table>
</body>
</html>

PS Thank you guys so much for your help, this means a LOT to me!!!

cyberRobot · August 15, 2014

It looks like it moved the Dreamweaver syntax error to line 35 though.

When including array variables like $row['Scott'] in a string, they need to be surrounded by curly brackets. You'll need to change lines like this to

<td>$row['Scott']</td>

To this

<td>{$row['Scott']}</td>

mac_gyver · August 16, 2014

some points that hopefully will help -

1) you should be development and testing code on a localhost development system, where you can easily set php's error_reporting to E_ALL and display_errors to ON in the php.ini so that ALL the errors php detects will be reported and displayed.

the last problem pointed out, about the syntax of using a php array variable inside a string, in your main file, would have been producing php parse/syntax errors, that will only show up if you have php's error_reporting/display_errors set in the php.ini.

2) the ->get_result() method is specific to a certain type of underlying database client driver being used (mysqlnd) and may be producing a php run-time error (which is why, again, you should be developing and debugging code on a system that has php's error_reporting/display_errors set so that php will help you.) the example code i am posting below uses another mysqlnd specific statement, ->fetch_all(), that may not work either. if these two statements produce undefined method php errors, the code using them will need to be rewritten or more simply, if it is an option available to you, use the PDO database statements as they are much easier to use.

3) in general, the code producing or containing any of your your html/css/javascript (client-side) markup should be separate from the portion of your code that knows how to retrieve data from the database. the code producing the html should have no database specific statements in it. this will allow you to more easily change the type of database statements being used or to delegate and divide the work among others. you would retrieve the data from your database and store it in a php array variable, then simply loop over that variable in place of the while(){} loop you have now that's making use of the msyqli fetch statement.

4) your code producing the table output can and should be written to be general purpose as well, so that it can display any amount of columns and rows that the query retrieves.

a rearrangement of your code that demonstrates (untested) these points (and while i was doing this i noticed an ->execute() method statement that wasn't, which would have been producing another php run time error) -

<?php
 
$mysqli = new mysqli("localhost", "guestuser", "guestuser", "SMQ");
if ($mysqli->connect_errno) {
echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
}
 
$validScott = $_GET['scott'];
 
$query = 'SELECT Scott, Den, Color, Cond, 70, 70J, 75, 75J, 80, 80J, 85, 85J, 90, 90J, 95, 95J, P98, 98J, 100 
FROM SMQSQL WHERE Scott = ?';
$stmt = $mysqli->prepare($query);
$stmt->bind_param('s',$validScott);
$stmt->execute();
$result = $stmt->get_result();
$results = $result->fetch_all(MYSQLI_ASSOC); // if the ->get_result() method is present, the ->fetch_all() method is also available, otherwise you will need to bind the result from the query and fetch it or use the more friendly PDO database library
$stmt->close();
 
// get data for table heading
$finfo = $result->fetch_fields();
$fields = array();
foreach ($finfo as $val){
$fields[] = $val->name;
}
 
// the above php code is the business logic, that knows what to do on the page and how to get data from the database
// the following code, is the presentation logic, that knows how to produce the output on the page
 
$heading = "<tr><td>".implode("</td><td>",$fields)."</td></tr>";
 
$queryResult = '';
foreach($results as $row){
$queryResult .= "<tr><td>".implode("</td><td>",$row)."</td></tr>";
}
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "[url=http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd]http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">[/url]
<html xmlns="[url=http://www.w3.org/1999/xhtml]http://www.w3.org/1999/xhtml">[/url]
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>Online SMQ</title>
</head>
<body>
 
<form name="searchForm" method="GET" action="#">
<input  type="text" name="scott">
<input  type="submit" name="submit" value="Search">
</form>
<p>
<table>
<?php echo $heading; ?>
<?php echo $queryResult; ?>
</table>
</body>
</html>

Disead · August 19, 2014

Thank you all, its getting closer, I can definitely tell... I have tried both the above suggestions, here are the results.

First, on the original code that everyone so far has been editing here and there (cyberRobot was the last to do so), it now looks like this:

<!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>Online SMQ</title>
</head>
<body>

<form name="searchForm" method="GET" action="#">
  <input  type="text" name="scott">
  <input  type="submit" name="submit" value="Search">
</form>
<p>
</html>
<?php

$mysqli = new mysqli("localhost", "guestuser", "guestuser", "SMQ");
if ($mysqli->connect_errno) {
    echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
}

$validScott = $_GET['scott'];

$stmt = $mysqli->prepare('SELECT Scott, Den, Color, Cond, 70, 70J, 75, 75J, 80, 80J, 85, 85J, 90, 90J, 95, 95J, P98, 98J, 100 FROM SMQSQL WHERE Scott = ?');
$stmt->bind_param('s',$validScott);
$stmt->execute;
$result = $stmt->get_result();
$stmt->close(); 

$queryResult = '';

while($row = $result->fetch_assoc()){

   $queryResult.= "
   <tr>
     <td>{$row['Scott']}</td>
     <td>{$row['Den']}</td>
     <td>{$row['Color']}</td>
	 <td>{$row['Cond']}</td>
	 <td>{$row['70']}</td>
	 <td>{$row['70J']}</td>
	 <td>{$row['75']}</td>
	 <td>{$row['75J']}</td>
	 <td>{$row['80']}</td>
	 <td>{$row['80J']}</td>
	 <td>{$row['85']}</td>
	 <td>{$row['85J']}</td>
	 <td>{$row['90']}</td>
	 <td>{$row['90J']}</td>
	 <td>{$row['95']}</td>
	 <td>{$row['95J']}</td>
	 <td>{$row['98']}</td>
	 <td>{$row['98J']}</td>
	 <td>{$row['100']}</td>
	 
   </tr>
   ";

}

?>

<!DOCTYPE html>

<table>
    <tr>
        <td>Scott</td>
        <td>Den</td>
        <td>Color</td>
        <td>Cond</td>
	<td>70</td>
	<td>70J</td>
	<td>75</td>
        <td>75J</td>
	<td>80</td>
	<td>80J</td>
	<td>85</td>
	<td>85J</td>
	<td>90</td>
	<td>90J</td>
	<td>95</td>
	<td>95J</td>
	<td>98</td>
	<td>98J</td>
	<td>100</td>
     <tr>
     <?php echo $queryResult; ?>
</table>
</body>
</html>

The syntax error is gone (thanks cyberRobot) but I am now getting this error when trying to execute:

Fatal error: Call to a member function bind_param() on a non-object in /home/myusername/public_html/SMQ.php on line 25

As far as the code given to me by mac_guyver (thank you!!!), I have not changed any of the code whatsoever, just loaded it in, set the correct password, and tested it. I got the same error here too:

Fatal error: Call to a member function bind_param() on a non-object in /home/myusername/public_html/SMQNEW.php on line 13

basically, both times, it throws the error based on this:

$stmt->bind_param('s',$validScott);

Hint: I checked the DB permissions of my user "guestuser" that I use to connect with, set the permissions to allow everything to check if this was the culprit, and it did NOT change the error.

Now what??? SOOOO CLOSE!!!!!!

I'm going to owe a lot of people a lot of beer (or other preferred consumable) when this is done...

Disead · August 19, 2014

Update:

Found the source of:

Fatal error: Call to a member function bind_param() on a non-object in /home/myusername/public_html/SMQ.php on line 25/13

It was a simple GIGO error on my part; I had accidentally added a P in front of the 98 in:

$stmt = $mysqli->prepare('SELECT Scott, Den, Color, Cond, 70, 70J, 75, 75J, 80, 80J, 85, 85J, 90, 90J, 95, 95J, 98, 98J, 100 FROM SMQSQL WHERE Scott = ?');

So, now that error is gone. Instead, I am now faced with the error:

Fatal error: Call to undefined method mysqli_stmt::get_result() in /home/jessicarmosher/public_html/SMQNEW.php on line 16/27 (Depending on whose code has been used)

What could be causing this one???

Hint: I have done some searching, and it seems that a lot of people with this error get it because using:

$result = $stmt->get_result();

requires the ability to use mysqlnd. I am running this rigged mess on godaddy, and it would seem to me that they should have this installed/enabled. Could this in any way be causing my headaches, or is it something else???

Just read here http://webhostingbits.com/2013/08/do-godaddy-linux-servers-have-mysqlnd-installed/ that apparently mysqlnd is not yet supported by godaddy. Does this mean I might have to instead work with BIND_RESULT and FETCH? So, how can this be written to work around this???

Fingers crossed...

Edited August 19, 2014 by Disead

mac_gyver · August 19, 2014

this is covered in point #2 in my post and the php.net documentation for msyqli prepared queries. the portion of the code using ->get_result() will need to be rewritten to use the ->bind_result() and ->fetch() methods.

if you continue to use mysqli statements, you will need to use a ->bind_result() statement to bind each column being selected in the query to a php variable (array entries will work), then, in your existing while(){} loop, use the ->fetch() method to get the data from the query into those variables.

if you instead switch to using PDO statements, you can avoid all of this mess, because no matter how you run the query (prepared statements or not, regardless of any driver or php version differences), you can use PDOStatement methods to fetch the result form the query.

Disead · August 19, 2014

if you continue to use mysqli statements, you will need to use a ->bind_result() statement to bind each column being selected in the query to a php variable (array entries will work), then, in your existing while(){} loop, use the ->fetch() method to get the data from the query into those variables.

OK, got it (sort of). I understand the premise of what you are stating. I unfortunately will have to stick with mysqli statements because the way the company wants it set up it HAS to run on the godaddy SQL server that is set up.

Could you do me a HUGE favor (you have already helped so much) and show me what that code needs to look like? I understand in what section each part needs to go in, but the syntax eludes me.

THANK YOU!!!

mac_gyver · August 19, 2014

you could of course write out a ->bind_result() statement that lists a $row['column_name'] variable for each column you have selected in the query, but that wouldn't be general purpose and would require that you do a lot of typing that the computer would be more than happy to save you from doing each time you write out or change a query.

fortunately, i have done this before, and when you see how much code this takes, you will see why we recommend using the PDO database library/class.

with respect to the code i posted as an example above, the following section -

$stmt->execute();

...

// the above php code is the business logic, that knows what to do on the page and how to get data from the database

is replaced with this -

$stmt->execute();

$meta = $stmt->result_metadata();
$variables = array(); // the 'bind_result' parameters
$data = array(); // array to reference to hold the actual fetched data
$fields = array(); // just the field names, for building the display header
while($field = $meta->fetch_field()){
    $variables[] = &$data[$field->name]; // parameters to 'bind_result' are references to the data array elements
    $fields[] = $field->name;
}

call_user_func_array(array($stmt, 'bind_result'), $variables);

$results = array();
$i=0;
while($stmt->fetch()){
    $results[$i] = array();
    foreach($data as $k=>$v){
        $results[$i][$k] = $v; // you must specifically access the key/value (otherwise you get a reference to the element in $data and they are all the last value fetched)
    }
    $i++;
}
$stmt->close();

// the above php code is the business logic, that knows what to do on the page and how to get data from the database

Sign In

Trying to get a simple PHP SQL search to work

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information