query problem with a basic search *may also be my bad coding please help* :(

[Simon180]

hello am trying to add a search option i found a small code that i have modifyed with my basic php skills but now i am stuck!

 

am trying to search a number of fields for items but not all at the time my problem is with the query i belive can sumone help me please?

 

i placed the query code in a line to make it easyer to read

 

thanks

 

<?php

  $pattern = '/^[  a-zA-Z]+/';

  if (isset($_POST['submit'])) { 
  if (isset($_GET['view'])) {
  
  if (preg_match($pattern, $_POST["sname"]) or
  preg_match($pattern, $_POST["sbrand"]) or
  preg_match($pattern, $_POST["stype"]) or
  preg_match($pattern, $_POST["sprice"])) {

  $name = $_POST["sname"];
  $brand = $_POST["sbrand"];
  $sprice = $_POST["sprice"];
  $stype = $_POST["stype"];
  $slimit = $_POST["slimit"];
  
  //connect to mysql
  //change user and password to your mySQL name and password
  mysql_connect("localhost","test","test"); 

  //select which database you want to edit
  mysql_select_db("dbob"); 
  
  //-query  the database table
  $sql = "SELECT * FROM tilesdata 

WHERE tilename 
LIKE '%$name%' 
AND tilebrand 
LIKE '%$brand%' 
AND ptype 
LIKE '%$stype%' 
AND tileprice 
LIKE '%$sprice%' 
ORDER by tilename 
LIMIT $slimit";
  
  //-run  the query against the mysql query function 
  $result = mysql_query($sql);
  
  //-create  while loop and loop through result set
  while($row = mysql_fetch_array($result)) {
        $title = $row['tilename'];
        $id = $row['id'];

  //-display error if result is null
  if ($result == '') {
  echo 'Sorry, no matching products were found!, please broaden your search.';
  }
  
  //-display the result of the array
  echo'<a href="index.php?view=product&id='. $id .'"><strong>'. $title .'</strong></a><br>';
  }
  } else {
  //-display error if input is null
  echo 'Please enter a search query.';
    }
   }
  } 

?>

[schilly]

for debug purposes change this

$result = mysql_query($sql);

 

to

$result = mysql_query($sql) or die(mysql_error());

 

the query looks good though.

 

what's happening when you try it?

[darkfreaks]

try this and tell us what it outputs:

<?php
  $pattern = '/^[  a-zA-Z]+/';
  if (isset($_POST['submit']))
    {
      if (isset($_GET['view']))
        {
          if (preg_match($pattern, $_POST["sname"])||preg_match($pattern, $_POST["sbrand"])||preg_match($pattern, $_POST["stype"])||preg_match($pattern, $_POST["sprice"]))
            {
              $name = $_POST["sname"];
              $brand = $_POST["sbrand"];
              $sprice = $_POST["sprice"];
              $stype = $_POST["stype"];
              $slimit = $_POST["slimit"];
              //connect to mysql
              //change user and password to your mySQL name and password
              $link=mysql_connect("localhost", "test", "test");
if (!$link) {
    die('Could not connect: ' . mysql_error());
}
              //select which database you want to edit
              mysql_select_db("dbob");
              //-query  the database table
              $sql = "SELECT * FROM tilesdata 

WHERE tilename 
LIKE '%$name%' 
AND tilebrand 
LIKE '%$brand%' 
AND ptype 
LIKE '%$stype%' 
AND tileprice 
LIKE '%$sprice%' 
ORDER by tilename 
LIMIT $slimit";
              //-run  the query against the mysql query function 
              $result = mysql_query($sql) or die(mysql_error());
              //-create  while loop and loop through result set
              while ($row = mysql_fetch_array($result))
                {
                  $title = $row['tilename'];
                  $id = $row['id'];
                  //-display error if result is null
                  if ($result == '')
                    {
                      echo 'Sorry, no matching products were found!, please broaden your search.';
                    }
                  //-display the result of the array
                  echo '<a href="index.php?view=product&id=' . $id . '"><strong>' . $title . '</strong></a><br>';
                }
            }
          else
            {
              //-display error if input is null
              echo 'Please enter a search query.';
            }
        }
    }
?>

[Simon180]

i get following error

 

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource

[darkfreaks]

i edited it try now:

<?php
  $pattern = '/^[  a-zA-Z]+/';
  if (isset($_POST['submit']))
    {
      if (isset($_GET['view']))
        {
          if (preg_match($pattern, $_POST["sname"])||preg_match($pattern, $_POST["sbrand"])||preg_match($pattern, $_POST["stype"])||preg_match($pattern, $_POST["sprice"]))
            {
              $name = $_POST["sname"];
              $brand = $_POST["sbrand"];
              $sprice = $_POST["sprice"];
              $stype = $_POST["stype"];
              $slimit = $_POST["slimit"];
              //connect to mysql
              //change user and password to your mySQL name and password
              $link=mysql_connect('localhost', 'test', 'test') or die('could not connect to MYSQL Server!"');

              //select which database you want to edit
              mysql_select_db('dbob',$link)  or die('Could not select database.');
              //-query  the database table
              $sql = "SELECT * FROM tilesdata 

WHERE tilename 
LIKE '%$name%' 
AND tilebrand 
LIKE '%$brand%' 
AND ptype 
LIKE '%$stype%' 
AND tileprice 
LIKE '%$sprice%' 
ORDER by tilename 
LIMIT $slimit";
              //-run  the query against the mysql query function 
              $result = mysql_query($sql) or die(mysql_error());
              //-create  while loop and loop through result set
              while ($row = mysql_fetch_array($result))
                {
                  $title = $row['tilename'];
                  $id = $row['id'];
                  //-display error if result is null
                  if ($result == '')
                    {
                      echo 'Sorry, no matching products were found!, please broaden your search.';
                    }
                  //-display the result of the array
                  echo '<a href="index.php?view=product&id=' . $id . '"><strong>' . $title . '</strong></a><br>';
                }
            }
          else
            {
              //-display error if input is null
              echo 'Please enter a search query.';
            }
        }
    }
?>

[Simon180]

still never worked this is a pain in the bum haha

 

when i goto do a search it will not do anything unless i use the type drop down

please try yourself at

 

http://leo.zola4u.com/index.php?view=search *this is not a advert*

 

first try with just a tile name IE white or black or just name tile and you notice it not show anything and then if u reload page and use product type and then search it will find products easy werid ....

[schilly]

this all stems around your query. i would use phpmyadmin and just test queries on your db until you get the results you want.

 

the query looks fine to me though i'm unsure what LIKE "%%" will do if one of the fields is blank. I assume it will match anything but never tested that.

 

i would also echo your sql query out so you can see exactly what the db is running.