SQL query help

[extraspecialbitter]

I've written exactly one PHP program thus far - search against a text file using "preg_match".  It looked something like this (and it worked):

 

<pre>
<?php
$string = $_POST['keywords'];
$matches = 0;
$file = fopen("archive.txt", "r") or die("Cannot open file!\n");
while ($line = fgets($file, 1024)) {
    if (preg_match("/$string/", $line)) {
        echo $line;
        echo "\n";
        $matches = 1;
    }
}

if ($matches == 0) {
        echo "sorry, I haven’t written a haiku about ".$string." yet.";
}

fclose($file);
?>
</pre>

 

Now I need to match a string in a MySQL table and return the row as a match, and I'm struggling with the syntax.  Here's what I've come up with so far.  It produces a blank page.  

<pre>
<?php
$string = $_POST['keywords'];
// Connect to Database
mysql_connect("localhost", "user", "password") or die(mysql_error());
 mysql_select_db("archive") or die(mysql_error());
 $data = mysql_query("SELECT * FROM archive_text WHERE text LIKE $string")
 or die(mysql_error());
 if ($data = "") {
    echo "sorry, I haven’t written anything about ".$string." yet.";
 }
    echo $data;
    echo "\n";
 }

?>
</pre>

 

In both cases, the string is passed from a simple HTML search page to the PHP program via a post.  

 

Any pointers would be appreciated.

[Jessica]

if ($data = "") {

 

= is assignment.

== is comparison.

 

You're also missing a call to mysql_fetch_assoc or similar.

[Barand]

Also

 

 - string values should be inside single quotes

 - LIKE uses wildcards

 

so

 

 

WHERE text LIKE '%$string%'

[Jessica]

You can use like without them, can't you?

[Barand]

Yes, but then you may as well just use "="

[haku]

I believe (maybe incorrectly) using like without % takes longer than = as well.

[Barand]

Haku, use of indexes would explain that

 

mysql> EXPLAIN SELECT * FROM votes WHERE type = 12;
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
|  1 | SIMPLE      | votes | ref  | idx2          | idx2 | 5       | const | 2090 | Using where |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+


mysql> EXPLAIN SELECT * FROM votes WHERE type LIKE 12;
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | votes | ALL  | idx2          | NULL | NULL    | NULL | 183062 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+

[haku]

Thanks Barand. If I'm reading that right, it confirms what I believed.

[extraspecialbitter]

Thank you for all of the replies!  I've fixed the comparison statement and the string in the SQL query but am still returning no results.  As Jessica mentioned, I suspect I need the mysql equivalent of the "preg_match" I used against a text file in my original example.

 

To reduce scrolling, here's the revised snippet:

 

<pre>
<?php
$string = $_POST['keywords'];
// Connect to Database
mysql_connect("localhost", "user", "password") or die(mysql_error());
 mysql_select_db("archive") or die(mysql_error());
 $data = mysql_query("SELECT * FROM archive_text WHERE text LIKE '$%string%'")
 or die(mysql_error());
 if ($data == "") {
    echo "sorry, I haven’t written anything about ".$string." yet.";
 }
    echo $data;
    echo "\n";
 }


?>
</pre>

[Barand]

WHERE text LIKE '%$string%'

[extraspecialbitter]

Thank you for the quick reply - I've made the change, but still get a blank page as a result of a search.

 

I'm baffled that neither clause of the if/then statement is executed.  Back to the drawing board I guess...

[Barand]

put

 

 

error_reporting(-1);

 

at top of the script;

[extraspecialbitter]

I did some additional troubleshooting and am now finally past the blank page problem.  Now, regardless of the query string, the only thing that gets returned to the browser is the mysterious "Resource id #3".

 

Here's the snippet as it exists now:

 

<pre>
<?php
error_reporting(-1);
$string = $_POST['keywords'];
// Connect to Database
mysql_connect("localhost", "user", "password") or die(mysql_error());
 mysql_select_db("archive") or die(mysql_error());
 $data = mysql_query("SELECT * FROM archive_test WHERE text LIKE '%$string%'");
 if (!$data) {
    $message  = 'Invalid query: ' . mysql_error() . "\n";
    $message .= 'Whole query: ' . $query;
    die($message);
 }

 if ($data == "") {
    echo "sorry, I haven’t written anything about ".$string." yet.";
 }
 {
    echo $data;
    echo "\n";
 }

?>
</pre>

[PaulRyan]

You are to return the data via mysql_fetch_assoc or mysql_fetch_array

[extraspecialbitter]

My thanks to everyone for the help.  I finally have the search function working exactly the way I'd like.  Here's the final snippet:

 

<pre>
<?php
error_reporting(-1);
$string = $_POST['keywords'];
// Connect to Database
mysql_connect("localhost", "user", "password") or die(mysql_error());
 mysql_select_db("archive") or die(mysql_error());
 $query = mysql_query("SELECT * FROM archive_test WHERE text LIKE '%$string%'");
 if (!$query) {
    $message  = 'Invalid query: ' . mysql_error() . "\n";
    $message .= 'Whole query: ' . $query;
    die($message);
 }

 while($row=mysql_fetch_row($query))
 {
    echo $row[0];
    echo "     $row[1]";
    echo "\n";
    echo "\n";
 }

?>
</pre>