MySQL

mysql search and display data help....

Please support our MySQL advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

•

Join Date: Aug 2006

Posts: 58

Reputation: desiguru is an unknown quantity at this point

Solved Threads: 1

desiguru

Offline

Junior Poster in Training

mysql search and display data help....

Aug 3rd, 2006

First of all this is a crazy thing I have just done.. so please sit back and read the whole post.............. Today I finished my math final and decided to learn a new programming language..... MYSQL. So I was able make my way to http://www.freewebmasterhelp.com/tutorials/phpmysql using Google I followed most of the tutorial and got the database setup... up until part 4; Everything works great and now I am just curious that how can I make a small page called search.php in which I can make a small search box. and have only one result display from the MySQL?

•

Join Date: Jul 2006

Posts: 831

Reputation: pritaeas will become famous soon enough

Solved Threads: 136

pritaeas

Offline

Practically a Posting Shark

Re: mysql search and display data help....

Aug 3rd, 2006

Create a html form like this:

 Help with Code Tags 
 MySQL Syntax (Toggle Plain Text) 
<form action="search.php" method="post">
<input type="text" name="search"><br>
<input type="submit">
</form>
<form action="search.php" method="post">
<input type="text" name="search"><br>
<input type="submit">
</form>

search.php

 Help with Code Tags 
 MySQL Syntax (Toggle Plain Text) 
<?php
  $search = "%" . $_POST["search"] . "%";
 
  mysql_connect ("localhost", "myuser", "mypassword");
  mysql_select_db ("mydatabase");
 
  $query = "SELECT mycolumn FROM mytable WHERE mycolumn LIKE '$search'";
  $result = mysql_query ($query);
  if ($result) {
    while ($row = mysql_fetch_array ($result)) {
      echo $row[0] . "<br>";
    }
  }
?>
<?php
  $search = "%" . $_POST["search"] . "%";

  mysql_connect ("localhost", "myuser", "mypassword");
  mysql_select_db ("mydatabase");

  $query = "SELECT mycolumn FROM mytable WHERE mycolumn LIKE '$search'";
  $result = mysql_query ($query);
  if ($result) {
    while ($row = mysql_fetch_array ($result)) {
      echo $row[0] . "<br>";
    }
  }
?>

"If it is NOT source, it is NOT software."
-- NASA

•

Join Date: Aug 2006

Posts: 58

Reputation: desiguru is an unknown quantity at this point

Solved Threads: 1

desiguru

Offline

Junior Poster in Training

Re: mysql search and display data help....

Aug 3rd, 2006

I got it to work but it displays only one column instead of showing all of the columns from the database.

Is there anyway I can show all of the columns?

•

Join Date: Jul 2006

Posts: 831

Reputation: pritaeas will become famous soon enough

Solved Threads: 136

pritaeas

Offline

Practically a Posting Shark

Re: mysql search and display data help....

Aug 6th, 2006

 Help with Code Tags 
 MySQL Syntax (Toggle Plain Text) 
if ($result) {
    while ($row = mysql_fetch_array ($result)) {
      foreach ($row as $key => $value) {
        echo "$key = $value<br>";
      }
      echo "<hr>";
    }
  }
if ($result) {
    while ($row = mysql_fetch_array ($result)) {
      foreach ($row as $key => $value) {
        echo "$key = $value<br>";
      }
      echo "<hr>";
    }
  }

Last edited by pritaeas; Aug 6th, 2006 at 3:07 pm.

"If it is NOT source, it is NOT software."
-- NASA

•

Join Date: Jan 2007

Posts: 1

Reputation: homienick is an unknown quantity at this point

Solved Threads: 0

homienick

Offline

Newbie Poster

Re: mysql search and display data help....

Oct 24th, 2007

I'm trying to do just this, but when a persons profile loads, it grabs their user_id as the page is loading, then searches the database for that user_id, and displays the rows it found that contained that user_id.

basically the same thing here, but without the search form.

would i need to change this line, or delete it?:

 Help with Code Tags 
 MySQL Syntax (Toggle Plain Text) 
 $search = "%" . $_POST["search"] . "%";
 $search = "%" . $_POST["search"] . "%";

in the previously posted code:

 Help with Code Tags 
 MySQL Syntax (Toggle Plain Text) 
<?php
  $search = "%" . $_POST["search"] . "%";
 
  mysql_connect ("localhost", "myuser", "mypassword");
  mysql_select_db ("mydatabase");
 
  $query = "SELECT mycolumn FROM mytable WHERE mycolumn LIKE '$search'";
  $result = mysql_query ($query);
  if ($result) {
    while ($row = mysql_fetch_array ($result)) {
      echo $row[0] . "<br>";
    }
  }
?>
<?php
  $search = "%" . $_POST["search"] . "%";

  mysql_connect ("localhost", "myuser", "mypassword");
  mysql_select_db ("mydatabase");

  $query = "SELECT mycolumn FROM mytable WHERE mycolumn LIKE '$search'";
  $result = mysql_query ($query);
  if ($result) {
    while ($row = mysql_fetch_array ($result)) {
      echo $row[0] . "<br>";
    }
  }
?>

•

Join Date: Sep 2007

Posts: 1,080

Reputation: SheSaidImaPregy is an unknown quantity at this point

Solved Threads: 68

SheSaidImaPregy SheSaidImaPregy is offline

Offline

Veteran Poster

Re: mysql search and display data help....

Oct 26th, 2007

For all:
all the "%" do is tell MySQL it is a wild card. So "am" with "%am%" pulls up anything with any number of letters before the combo "am" and any number of letters after:

name - n am e
tame - t am e
madam - mad am
ambulance - am bulance

understand? So if you're searching the database for a userid, you do not want the LIKE scenario, you want the normal "userid" not, "%userid%".
Furthermore, "%am" will pull only words beginning with anything but ending with am, like "sam", and same as the other way "am%" pulls words like "ambuleance".

For your search page, if you only want one result, add this line to your SQL query: "LIMIT 1"
This way it will only return one result and not waste memory holding the other 200 results that are being unused.

For homienick
So, following the code above as I am not a PHP programmer, use it like below to pull your users info:

 Help with Code Tags 
 MySQL Syntax (Toggle Plain Text) 
<?php
  $userid = $_POST["userid"];
 
  mysql_connect ("localhost", "myuser", "mypassword");
  mysql_select_db ("mydatabase");
 
  $query = "SELECT mycolumn FROM mytable WHERE mycolumn=$userid"
  $result = mysql_query ($query);
  if ($result) {
    //if this userid EXISTS, DO your coding here
  }ELSE{
    //if this userid does NOT exist, DO your coding here
  }
?>
<?php
  $userid = $_POST["userid"];

  mysql_connect ("localhost", "myuser", "mypassword");
  mysql_select_db ("mydatabase");

  $query = "SELECT mycolumn FROM mytable WHERE mycolumn=$userid"
  $result = mysql_query ($query);
  if ($result) {
    //if this userid exists, do your coding here
  }else{
    //if this userid does not exist, do your coding here
  }
?>