Trying to get a simple PHP SQL search to work

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
Disead
Forum Newbie
Posts: 5
Joined: Thu Aug 14, 2014 5:46 am

Trying to get a simple PHP SQL search to work

Post by Disead »

I need your help!

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 my 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 on godaddy with no errors. I can use the cPanel 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, very logical. However, I have been pulling my hair out for days trying to get a simple search function as an actual .php 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:

Code: Select all

<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!!!
jdhmtl
Forum Newbie
Posts: 18
Joined: Tue Aug 12, 2014 7:33 am

Re: Trying to get a simple PHP SQL search to work

Post by jdhmtl »

You haven't actually stated what isn't working, but one thing that jumps out is $98J, etc. Variables cannot start with numbers. Moreover, if you're just going to be echoing it out, there's not much point creating separate variables.

Code: Select all

$foo = $row['foo'];
echo $foo;

// vs.
echo $row['foo'];
User avatar
social_experiment
DevNet Master
Posts: 2793
Joined: Sun Feb 15, 2009 11:08 am
Location: .za

Re: Trying to get a simple PHP SQL search to work

Post by social_experiment »

jdhmtl wrote:You haven't actually stated what isn't working
agreed, it helps if you specify what the problem is.

Code: Select all

<?php
$result=mysql_query($sql) or die('Error found ' . mysql_error());
?>
If the problem is with the syntax in your SQL query you can find it by using the mysql_error() function, as you did with the connection part. Very helpful.

Btw, have you solved this problem?
“Don’t worry if it doesn’t work right. If everything did, you’d be out of a job.” - Mosher’s Law of Software Engineering
Post Reply