mysql search using for loop from php

Question

i am a beginner. but I'm practicing a lot for few days with php mysql, and I am trying to use for loop to search an exploded string, one by one from mysql server. Till now I have no results. I'm giving my codes,

<?php
// Example 1
 $var = @$_GET['s'] ;

$limit=500;


echo "  ";

echo "$var";

echo "  ";

 $trimmed_array = explode(" ", $var);


echo "$trimmed_array[0]"; // piece1

echo "   ";
$count= count($trimmed_array);
echo $count;

for($j=0;$j<$count;$j++)
{
e    cho "$trimmed_array[$j]";;
echo "  ";
}

echo "   ";




for($i=0; $i<$count ; $i++){
                 $query = "select * from book where name like \"%$trimmed_array[$i]%\" order by name";

         $numresults=mysql_query($query);
         $numrows =mysql_num_rows($numresults);

 if ($numrows == 0)
  {
  echo "<h4>Results</h4>";
  echo "<p>Sorry, your search: &quot;" . $trimmed_array[i] . "&quot; returned zero results</p>";
  }


  if (empty($s)) {
  $s=0;
  }


  $query .= " limit $s,$limit";
  $result = mysql_query($query) or die("Couldn't execute query");




echo "<p>You searched for: &quot;" . $var . "&quot;</p>";





echo "Results<br /><br />";


$count=1;



  while ($row= mysql_fetch_array($result)) {
  $name = $row["name"];
  $publisher=$row["publisher"];
  $total=$row["total"];
  $issued=$row["issued"];
  $available=$row["available"];
  $category=$row["category"];

  echo "<table border='1'><tr><td>$count)</td><td>$name&nbsp;</td><td>$publisher&nbsp;</td><td>$total&nbsp;</td><td>$issued&nbsp;</td><td>$available&nbsp;</td><td>$category&nbsp;</td></tr></table>" ;
  $count++ ;
  }
   }

?>

are you getting any errors and have you check that there are values in database? — S L
– S L, Commented Feb 21, 2011 at 7:29

eav · Accepted Answer · 2011-02-21 07:29:20Z

1

In your case, you do for every record in your array ($trimmed_array) a new select. Thats not really good. It would be better when you create just one select... For example this:

// you need 1=1 for example when $i<count is false...
$baseQuery = "select * from book where 1=1";
$query = $baseQuery;

for($i=0; $i<$count ; $i++){
    $query .= " OR name like ?";
}
// do your ordering:
$query.= " order by name";

But what does this "?" mean? --> Do you know what sql-injection means? somebody could really easy put some information in this array wich could give any information about your database.. therefore you have to escape every userinput...

i like the mysqli package in php5. watch this example:

$query = "SELECT `id` FROM employees WHERE `name`=?";

// Setup parameter to be bound into query
$name = "Joey";

// Get instance of statement
$stmt = $mysqli->stmt_init();

// Prepare Query
if($stmt->prepare($query)){

  // Bind Parameters [s for string]
  $stmt->bind_param("s",$name);

  // Execute statement
  $stmt->execute();

  // Bind result variables
  $stmt->bind_result($employee_id);

  // Fetch Value
  $stmt->fetch();

  // Echo results
  echo "$name has an ID of $employee_id";

  // Close Statement
  $stmt->close();
}

answered Feb 21, 2011 at 7:29

eav

2,1917 gold badges26 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Your Common Sense Over a year ago

in fact, all query data (not user input! - that is wrong and deceiving statement!) should be escaped or binded not because someone could put some information in this array blah-blah, but just because there can be some service characters in the data, that will corrupt the query. Even when no one "someone" around.

Your Common Sense Over a year ago

+1 though for non-literal but proper answer - extremely rare behavior here.

Your Common Sense Over a year ago

however, you have to check array elements for emptyness. and 1=1 looks dirty

eav Over a year ago

this might be better: if(count($array)>0) { //do your querys :-) }

Mindaugas · Accepted Answer · 2011-02-21 07:29:51Z

0

Damn, your code really extremely crazy. Here you example about how to work with this:

<?php
   $var = $_GET['s'];
   $exp = explode(" ",$var);
   $total = count($exp) - 1;
   for($i = 0; $i <= $total; $i++) {
      echo "Search for: " . $exp[$i] ."\n";
      $sql = mysql_query("SELECT * FROM `book` WHERE `name` LIKE '%" . mysql_real_escape_string($exp[$i]) ."%'") or die(mysql_error());
      if (mysql_fetch_num($sql) != 0) {
         // Somthing found
      }
   }
?>

answered Feb 21, 2011 at 7:29

Mindaugas

3261 gold badge5 silver badges17 bronze badges

Comments

superUntitled · Accepted Answer · 2011-02-21 07:31:48Z

0

You have an error on line 25,

e cho "$trimmed_array[$j]";;

should be echo "$trimmed_array[$j]";

Also, it seems that you are using $GET_[] variables, which are passed via the url string, which does not allow spaces. On line 15, you are splitting the array with explode(" ", $var);

I would also urge you, if you have not, look into sanitizing your database queries.

edited Feb 21, 2011 at 7:31

answered Feb 21, 2011 at 7:24

superUntitled

22.6k30 gold badges86 silver badges113 bronze badges

Collectives™ on Stack Overflow

mysql search using for loop from php

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related