[SOLVED] PHP/MySQL/Form Search ?

emcgfx · July 25, 2009

Hi guys, I'm stuck with this code I've been trying to get to work. What I'm trying to do here is to use switch, form and mysql search. My add and del features work except the echo result part which is $status. But my search feature is not working at all. If you know what I'm doing wrong please help me out, thank you so much. I know its something very simple, its just I'm just learning this :-)

NOTE: DB info has been removed for security

<?php

  $hostname = "";
  $database = "";
  $username = "";
  $password = "";
  
  if(!isset($PHP_SELF)) $PHP_SELF = $_SERVER['PHP_SELF'];
  if(!isset($_POST['action'])) $_POST['action'] = NULL;
  if(!isset($status)) $status = NULL;
  if(!isset($_POST['find'])) $find['find'];
  if(!isset($_POST['named'])) $named['named'];
  if(!isset($_POST['name'])) $name['name'];
  if(!isset($_POST['age'])) $name['age'];
  
  function escape($string) {
    if(get_magic_quotes_gpc()) $string = stripslashes($string);
    return mysql_real_escape_string($string);
  }
  
  mysql_connect($hostname, $username, $password) or die(mysql_error());
  mysql_select_db($database) or die(mysql_error());
  
  switch($_POST['action']) {
    case "add":
      mysql_query("INSERT INTO example (name, age) VALUES('".escape($_POST['name'])."', 
        '".escape($_POST['age'])."' )") or die(mysql_error());
      $status = "$name added to database!";
      break;
    case "del":
      mysql_query("DELETE FROM example WHERE name='".escape($_POST['named'])."' ") 
        or die(mysql_error());
      $status = "$named removed from database!";
      break;
    case "search":
      /*mysql_query("SELECT * FROM example WHERE name='%".escape($_POST['search'])."%' ") 
        or die(mysql_error());*/
      
      $query = mysql_query("SELECT * FROM example WHERE name LIKE '".escape($_POST['find'])."' ORDER BY name");
      
      while ($row = mysql_fetch_array($query)) {
        echo $row['name'];
        echo "<br/>";
      } 
      break;
  }

?>
<html>
  <head>
    <title>MySQL/PHP</title>
  </head>
<body>
  <?php echo "$status"; ?> <br/>
  Add Entry in Database:
  <form method="post" action="<?php echo $PHP_SELF ?>">
    Name: <input type="text" size="25" maxlength="50" name="name"><br/>
    Age: <input type="text" size="5" maxlength="3" name="age"><br/>
    <input type="hidden" value="add" name="action">
    <input type="submit" value="Submit">
  </form>
  Delete Entry from Database:
  <form method="post" action="<?php echo $PHP_SELF ?>">
    Name: <input type="text" size="25" maxlength="50" name="named"><br/>
    <input type="hidden" value="del" name="action">
    <input type="submit" value="Submit">
  </form>
  Search Entry from Database:
  <form method="post" action="<?php echo $PHP_SELF ?>">
    Name: <input type="text" size="25" maxlength="50" name="search"><br/>
    <input type="hidden" value="find" name="action">
    <input type="submit" value="Submit">
  </form>
</body>
</html>

ignace · July 25, 2009

These lines do nothing (well the body I mean):

if(!isset($_POST['find'])) $find['find'];
if(!isset($_POST['named'])) $named['named'];
if(!isset($_POST['name'])) $name['name'];
if(!isset($_POST['age'])) $name['age'];

Note the use of %:

$query = mysql_query("SELECT * FROM example WHERE name LIKE '%".escape($_POST['find'])."%' ORDER BY name");

emcgfx · July 25, 2009

Hi ignace Thank you for your reply.

OK so if I use $_POST in my switch and the mysql_query's I don't need the following?

if(!isset($_POST['find'])) $find['find'];
if(!isset($_POST['named'])) $named['named'];
if(!isset($_POST['name'])) $name['name'];
if(!isset($_POST['age'])) $name['age'];

Also, I have tried using it like you said, as you can see with my commented out line of code. Which looks very simular to the one you showed me. But when I submit the search form, I don't see any result $status back just blank.

Also, I don't see any $status when I add or delete item from mysql database, all I see is "added to database!" and "removed from database!". What am I doing wrong here?

ignace · July 25, 2009

But when I submit the search form, I don't see any result $status back just blank.

print_r($_POST);

verify everything you need is posted.

OK so if I use $_POST in my switch and the mysql_query's I don't need the following?

You don't need them anyhow as they are doing nothing.

Also, I don't see any $status when I add or delete item from mysql database, all I see is "added to database!" and "removed from database!". What am I doing wrong here?

$status = "$name added to database!";

$name is an array with a key and null as a value as defined here:

if(!isset($_POST['name'])) $name['name']/* $name = array ( 'name' => null ) */;

<?php

  $hostname = "";
  $database = "";
  $username = "";
  $password = "";
  
  $PHP_SELF = !isset($PHP_SELF) ? $_SERVER['PHP_SELF'] : $PHP_SELF;
  $action = !empty($_POST['action']) ? $_POST['action'] : NULL;
  $status = !empty($_POST['status']) ? $_POST['status'] : NULL;
  $find = !empty($_POST['find']) ? $_POST['find'] : NULL;
  $name = !empty($_POST['name']) ? $_POST['name'] : NULL;
  $age = !empty($_POST['age']) ? $_POST['age'] : NULL;
  
  function escape($string) {
    if(get_magic_quotes_gpc()) $string = stripslashes($string);
    return mysql_real_escape_string($string);//only works if their is a db connection available.
  }
  
  mysql_connect($hostname, $username, $password) or die(mysql_error());
  mysql_select_db($database) or die(mysql_error());
  
  switch($action) {
    case "add":
      mysql_query("INSERT INTO example (name, age) VALUES('".escape($name)."', 
        '".escape($age)."' )") or die(mysql_error());
      $status = "$name added to database!";//overwrites the previous declared $status
      break;
    case "del":
      mysql_query("DELETE FROM example WHERE name='".escape($named)."' ") 
        or die(mysql_error());
      $status = "$named removed from database!";//overwrites the previous ..
      break;
    case "search":
      /*mysql_query("SELECT * FROM example WHERE name='%".escape($find)."%' ") 
        or die(mysql_error());*/
      
      $query = mysql_query("SELECT * FROM example WHERE name LIKE '".escape($find)."' ORDER BY name");
      
      while ($row = mysql_fetch_array($query)) {
        echo $row['name'];
        echo "<br/>";
      } 
      break;
  }

?>
<html>
  <head>
    <title>MySQL/PHP</title>
  </head>
<body>
  <?php echo "$status"; ?> <br/>
  Add Entry in Database:
  <form method="post" action="<?php echo $PHP_SELF ?>">
    Name: <input type="text" size="25" maxlength="50" name="name"><br/>
    Age: <input type="text" size="5" maxlength="3" name="age"><br/>
    <input type="hidden" value="add" name="action">
    <input type="submit" value="Submit">
  </form>
  Delete Entry from Database:
  <form method="post" action="<?php echo $PHP_SELF ?>">
    Name: <input type="text" size="25" maxlength="50" name="named"><br/>
    <input type="hidden" value="del" name="action">
    <input type="submit" value="Submit">
  </form>
  Search Entry from Database:
  <form method="post" action="<?php echo $PHP_SELF ?>">
    Name: <input type="text" size="25" maxlength="50" name="search"><br/>
    <input type="hidden" value="find" name="action">
    <input type="submit" value="Submit">
  </form>
</body>
</html>

emcgfx · July 26, 2009

Thank You, ignace I got it working.

emcgfx · July 26, 2009

ok one more question, how doe I also search for "age".

This is the working code for searching by "name".

$query = mysql_query("SELECT * FROM example WHERE name LIKE 
        '%".escape($_POST['search'])."%' ORDER BY name");

Thanks.

mmarif4u · July 26, 2009

Do you want to use both in the same search function query?

If yes then:

$query = mysql_query("SELECT * FROM example WHERE name LIKE
        '%".escape($_POST['search'])."%' OR '%$age%' ORDER BY name");

You can AND also instead of OR, its up to your need.

emcgfx · July 26, 2009

Yes, but its not working and where do you get $age? I need to search for age from database not to have another variable for it.

Now this following code works for searching "age", but now "name" is not working.

$query = mysql_query("SELECT * FROM example WHERE name OR age LIKE
        '%".escape($_POST['search'])."%' ORDER BY name");

mmarif4u · July 26, 2009

I get age from here from your code, but mb i get the wrong code??

$age = !empty($_POST['age']) ? $_POST['age'] : NULL;

So you want to use one text search field and use for both age and name to search?

emcgfx · July 26, 2009

I don't use the following code at all now, since I have been told it does nothing when I use $_POST in mysql_query's them self's.

if(!isset($PHP_SELF)) $PHP_SELF = $_SERVER['PHP_SELF'];
  if(!isset($_POST['action'])) $_POST['action'] = NULL;
  if(!isset($status)) $status = NULL;
  if(!isset($_POST['find'])) $find['find'];
  if(!isset($_POST['named'])) $named['named'];
  if(!isset($_POST['name'])) $name['name'];
  if(!isset($_POST['age'])) $name['age'];

As for using the form to search for both name and age, how would I do that ? Here is the following code I use now in the form to search for name:

  Search Entry from Database:
  <form method="post" action="<?php echo $PHP_SELF ?>">
    Name: <input type="text" size="25" maxlength="50" name="search"><br/>
    <input type="hidden" value="search" name="action">
    <input type="submit" value="Submit">
  </form>

and this is the mysql code I use:

    case "search":
      $query = mysql_query("SELECT * FROM example WHERE name LIKE 
        '%".escape($_POST['search'])."%' ORDER BY name");
      
      while ($row = mysql_fetch_array($query)) {
        echo "ID: " . $row['id'];
        echo ", " . $row['name'];
        echo ", " . $row['age'] . "<br/>";
      }
      break;

emcgfx · July 26, 2009

I figured it out :idea:

$query = mysql_query("SELECT * FROM example WHERE name LIKE '%".escape($_POST['search'])."%' OR
        age LIKE '%".escape($_POST['search'])."%' ");

emcgfx · July 26, 2009

ok here is the new working code, now how would I show what has beend removed or added to/from database? Preferably in $status variable.

<?php

  // MySQL Auth
  $hostname = "";
  $database = "";
  $username = "";
  $password = "";
  
  // Protection from SQL Injections.
  function escape($string) {
    if(get_magic_quotes_gpc()) $string = stripslashes($string);
    return mysql_real_escape_string($string);
  }
  
  mysql_connect($hostname, $username, $password) or die(mysql_error());
  mysql_select_db($database) or die(mysql_error());
  
  switch($_POST['action']) {
    case "add":
      mysql_query("INSERT INTO example (name, age) VALUES('".escape($_POST['name'])."', 
        '".escape($_POST['age'])."' )") or die(mysql_error());
      $status = $name . "added to database!";
      break;
    case "del":
      mysql_query("DELETE FROM example WHERE name='".escape($_POST['name'])."' ") 
        or die(mysql_error());
      echo "$name removed from database!";
      break;
    case "search":
      $query = mysql_query("SELECT * FROM example WHERE name LIKE '%".escape($_POST['search'])."%' 
        OR age LIKE '%".escape($_POST['search'])."%' ORDER BY name ");
      
      while ($row = mysql_fetch_array($query)) {
        echo "ID: " . $row['id'];
        echo ", " . $row['name'];
        echo ", " . $row['age'] . "<br/>";
      }
      break;
  }

?>
<html>
  <head>
    <title>MySQL/PHP</title>
  </head>
<body>
  <?php echo "$status"; ?> <br/>
  Add Entry in Database:
  <form method="post" action="<?php echo $PHP_SELF ?>">
    Name: <input type="text" size="25" maxlength="50" name="name"><br/>
    Age: <input type="text" size="5" maxlength="3" name="age"><br/>
    <input type="hidden" value="add" name="action">
    <input type="submit" value="Submit">
  </form>
  Delete Entry from Database:
  <form method="post" action="<?php echo $PHP_SELF ?>">
    Name: <input type="text" size="25" maxlength="50" name="name"><br/>
    <input type="hidden" value="del" name="action">
    <input type="submit" value="Submit">
  </form>
  Search Entry from Database:
  <form method="post" action="<?php echo $PHP_SELF ?>">
    Name: <input type="text" size="25" maxlength="50" name="search"><br/>
    <input type="hidden" value="search" name="action">
    <input type="submit" value="Submit">
  </form>
</body>
</html>

emcgfx · July 26, 2009

I got it :-)

      $var = $_POST['name'];
      $status = "$var removed from database!";

or

      $var = $_POST['name'];
      $status = "$var added to database!";

emcgfx · July 26, 2009

or like this

$status = $_POST['name'] . " added to database!";
$status = $_POST['name'] . " removed from database!";

If you know of any other way, please reply ::)

emcgfx · July 26, 2009

ok now, I need to have some kind of errors in place, for example if I try to delete "name" BOB and its not in database, I would show message like "sorry, no such name". Any help would be nice. Will keep trying for now. :facepalm:

emcgfx · July 26, 2009

Maybe like to use if or something....

emcgfx · July 26, 2009

I figured it out, if you know better way on doing this please reply.

Here is how I've done it.

      $result = mysql_query("SELECT * FROM example WHERE name='".escape($_POST['name'])."' ") 
        or die(mysql_error());
      
      if (mysql_num_rows($result) > 0) {
        mysql_query("DELETE FROM example WHERE name='".escape($_POST['name'])."' ") 
          or die(mysql_error());
        $status = $_POST['name'] . " removed from database! <br/>";
      } else {
        $status = $_POST['name'] . " does not exist in database! <br/>";
      }

Sign In

[SOLVED] PHP/MySQL/Form Search ?

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

Join the conversation

Important Information