[RESOLVED] php/mysql search script error

[chrisdee]

Hello.

I have the following script that's supposed to search for title out of my mysql articles table :

<?php
#connects to the MySQL server
$username = "user";     

$password = "pass";      

$host     = "localhost";
$database = "my_db";    

$table    = "articles";

mysql_connect($host,$username,$password) or die(mysql_error());
mysql_select_db($database) or die(mysql_error());

if(!$_POST) {
 print '<form method="post" action="search.php">';
 print '<input type="text" name="title">';
 print '<input type="Submit" value="Search">';
 print '</form>';
}
else {
 $id=$_GET["id"];
 $title=$_POST["title"];

 $result = mysql_query("SELECT * FROM $table WHERE $title LIKE '%$title%'");

 while($row=mysql_fetch_array($result)) {
  $title=$row["title"];
  $id=$row["id"];

  echo "$title";
 }
}
?>

But I get the following error message when I enter a search term (title name of article) and try to run it by clicking on submit :

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /var/www/wen/search.php on line 16

Below is my mysql table :

mysql> describe articles;
+----------+------------------+------+-----+---------+----------------+
| Field    | Type             | Null | Key | Default | Extra          |
+----------+------------------+------+-----+---------+----------------+
| title    | varchar(256)     | NO   | UNI | NULL    |                | 
| content  | blob             | NO   |     | NULL    |                | 
| date     | varchar(256)     | NO   |     | NULL    |                | 
| id       | int(30) unsigned | NO   | PRI | NULL    | auto_increment | 
| category | varchar(128)     | NO   |     | NULL    |                | 
+----------+------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

mysql> 

Any ideas what I'm doing wrong ?

[rincewind456]

Change

$result = mysql_query("SELECT * FROM $table WHERE $title LIKE '%$title%'");

To:

$result = mysql_query("SELECT * FROM $table WHERE $title LIKE '%$title%'")  or die(mysql_error());

in order to see whats happening in the database.

[johanafm]

"WHERE $title = '%$title%'"

You probably want to drop the $ from the first $title. If I search for "something", the above will become

WHERE something = '%something%'

Echoing your SQL queries to screen is often helpful, and if that tells you nothing, trying to execute the exact same query through db CLI or any other means of direct input is often educational. And logging or echoing SQL errors is also a good idea in a development environment.

// mysql_query EITHER returns a result set resource OR boolean false.
$result = mysql_query("SELECT * FROM $table WHERE $title LIKE '%$title%'");
if (!$result) {
	echo mysql_errno() . ": " . mysql_error();
	// As the errors output tells you, here the call to mysql_fetch_array would be
	$row = mysql_fetch_array(false);
}
else {
	while($row = mysql_fetch_array($result))
}

Furethermore, you want to

// $id is not used anywhere, but if it's ever put to use, you want it to be safe
$id = isset($_GET['id']) ? intval($_GET['id']) : 0;
$title = isset($_POST['title']) ? mysql_real_escape_string($_POST['title']) : false;
// Your code would work without this change, but the where clause "like '%%' is useless
if ($title)
	$where = "WHERE title like '%$title%'";
else
	$where = '';

$result = mysql_query("SELECT * FROM $table " . $where);

[chrisdee]

rincewind456;10928514 wrote:

Change

$result = mysql_query("SELECT * FROM $table WHERE $title LIKE '%$title%'");

To:

$result = mysql_query("SELECT * FROM $table WHERE $title LIKE '%$title%'")  or die(mysql_error());

in order to see whats happening in the database.

Hello. Thanks for answer🙂. I tried to add or die(mysql_error()); as you wrote. When I try to search for e.g cv I get the following message :

Unknown column 'cv' in 'where clause'

I dont understand what that means.:eek:

[chrisdee]

johanafm;10928515 wrote:

"WHERE $title = '%$title%'"

You probably want to drop the $ from the first $title. If I search for "something", the above will become

WHERE something = '%something%'

Echoing your SQL queries to screen is often helpful, and if that tells you nothing, trying to execute the exact same query through db CLI or any other means of direct input is often educational. And logging or echoing SQL errors is also a good idea in a development environment.

// mysql_query EITHER returns a result set resource OR boolean false.
$result = mysql_query("SELECT * FROM $table WHERE $title LIKE '%$title%'");
if (!$result) {
	echo mysql_errno() . ": " . mysql_error();
	// As the errors output tells you, here the call to mysql_fetch_array would be
	$row = mysql_fetch_array(false);
}
else {
	while($row = mysql_fetch_array($result))
}

Furethermore, you want to

// $id is not used anywhere, but if it's ever put to use, you want it to be safe
$id = isset($_GET['id']) ? intval($_GET['id']) : 0;
$title = isset($_POST['title']) ? mysql_real_escape_string($_POST['title']) : false;
// Your code would work without this change, but the where clause "like '%%' is useless
if ($title)
	$where = "WHERE title like '%$title%'";
else
	$where = '';

$result = mysql_query("SELECT * FROM $table " . $where);

Thanks for your answer to. 🙂 I tried the last code you wrote, but get a blank page when I try to search for anything.

[johanafm]

Perhaps time to post your current code again.

[chrisdee]

Hello. I found a solution that works🙂

if(!$_POST) {
 print '<form method="post" action="search.php">';
 print '<input type="text" name="title">';
 print '<input type="Submit" value="Search">';
 print '</form>';
}
else {
$title=$_POST["title"];
$result = mysql_query("SELECT * FROM $table WHERE title LIKE '%{$title}%'") or die(mysql_error());

while($row=mysql_fetch_array($result)) {
  $title=$row["title"];
  $id=$row["id"];

  echo "<a class=cd href=index.php?id=$id>$title</a><br>";
}
}

Thanks for your help🙂

[johanafm]

You're still missing a crucial point:

$title = mysql_real_escape_string($_POST['title']);

[chrisdee]

Thank you very much. I'v added that now.🙂

Am I right in assuming this has to be done to all $_POST variables to avoid possible attack ?

[djjjozsi]

how to build the links a bit faster/safer:

printf('<a class="cd" href="index.php?id=%d">%s</a><br />' , $row["id"] , htmlspecialchars($row["title"]) ); 

[johanafm]

chrisdee;10928598 wrote:

Am I right in assuming this has to be done to all $_POST variables to avoid possible attack ?

Anything going into the database that is a string or date, yes. For numbers you can use intval($_POST['some_int']) or floatval() instead. The reason is to avoid sql injection.

To avoid XSS, when you send user input to a browser, use htmlentities(), regardless of wether you've stored the data in a DB or not.

[chrisdee]

Thanks again🙂

[qirat786]

Can any one help, please, i get the following error.

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /www/sites/lcn/www.simplepm.co.uk/web/login.php on line 23

The code is below, please help me, i have been trying to fix this error for ages can't seem to get it working

if (mysql_num_rows($result) != 1) {
$error = "Bad Login";
include "login.html";

Thanks

[djjjozsi]

if(mysql_error())
die("Error in query: ( $sql ) ... " . mysql_query() ."<br />");
if (mysql_num_rows($result) != 1) {
$error = "Bad Login";
    include "login.html";
} 

Show a bit more from your code, maybe your SQL query has mistakes.
And new problem goes into new thread.

[qirat786]

Thank you for that, i get the following error now

Warning: Wrong parameter count for mysql_query() in /www/sites/lcn/www.simplepm.co.uk/web/login.php on line 24
Error in query: ( ) ...

And i have put the full code below please help thanks you very much

<?php

//Database Information

$dbhost = "";
$dbname = "";
$dbuser = "";
$dbpass = "";

//Connect to database

mysql_connect ( $dbhost, $dbuser, $dbpass)or die("Could not connect: ".mysql_error());
mysql_select_db($dbname) or die(mysql_error());

session_start();
$username = $POST[‘username’];
$password = md5($POST[‘password’]);

$query = "select * from users where username=‘$username’ and password=‘$password’";

$result = mysql_query($query);

if(mysql_error())
die("Error in query: ( $sql ) ... " . mysql_query() ."<br />");
if (mysql_num_rows($result) != 1) {
$error = "Bad Login";
include "login.html";
}

else {
$_SESSION[‘username’] = "$username";
include "memberspage.php";
}

?>

Please help thank you :-)

[rincewind456]

qirat786 this was also posted in another thread that had nothing to do with your problem, and has been moved to here please don't post in threads that are nothing to do with the problem you are having.

Also please use the BB [ php] tags to quote you code.

[qirat786]

Hi

I am having this problem, been trying to resolve it for ages but dont know what to do, please help please

the error i am getting is:

Error 2005 : Unknown MySQL server host '.lcn.com' (3)

The code is below please help

<!DOCTYPE HTML PUBLIC
"-//W3C//DTD HTML 4.0 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<title>Browse Upload Files</title>
</head>
<body bgcolor="white">

<?php
include 'db.inc';

$query = "SELECT id, shortName, mimeName FROM files";

if (!($connection = @ mysql_pconnect("$database.lcn.com",
"$username",
"$password")))
showerror();

if (!mysql_select_db("files", $connection))
showerror();

if (!($result = @ mysql_query ($query, $connection)))
showerror();
?>
<h1>Image database</h1>

<h3>Click <a href="insert.php">here</a> to

upload an image.</h3>
<?php
require 'disclaimer';

if ($row = @ mysql_fetch_array($result))
{
?>

<table>
<col span="1" align="right">
<tr>
   <th>Short description</th>
   <th>File type</th>
   <th>Image</th>

</tr>

<?php
do
{
?>
<tr>
<td><?php echo "{$row["shortName"]}";?></td>

<td><?php echo "{$row["mimeName"]}";?></td>
<td><?php echo "<img src=\"view.php?file={$row["id"]}\">";?></td>
</tr>
<?php
} while ($row = @ mysql_fetch_array($result));
?>
</table>
<?php
} // if mysql_fetch_array()
else
echo "<h3>There are no images to display</h3>\n";
?>
</body>
</html>

[djjjozsi]

the error message is telling: you did not specify the correct host to your database.

where this variable get a value: $database ?
img src .. You mean the link?

<a href="somewhere.php">Somewhere</a>

and in your future posts, please use the php BB tag around you php codes.

And here is an example:

<?php
include("db_conn.php");
$sql = "select * from files";
$res = mysql_query( $sql ) or die( "error:" . mysql_error() . ", In query:" . $sql );
if ( mysql_num_rows( $res ) == 0 )
    echo "files is empty...";
else {
    while ( $a = mysql_fetch_assoc( $res ) ) {
        echo "shortName:" . $a["shortName"] . "<br />";
        echo "mimeName:" . $a["mimeName"] . "<br />";
        echo "<hr />";
    } 
    unset( $a );
} 

?>