[RESOLVED] probably a silly question about searching column

christillis

Hi there,

I was really trying to avoid asking any stupid question, but I've been trying it for a while now.

This code works OK:

$sql = "SELECT projno, description FROM project_workdone WHERE projno LIKE '%".$projectnumber."%'";

projno = column
project_workdone = table

$projectnumber = input form field.

But I would like to do this instead:

$sql = "SELECT projno, description FROM project_workdone WHERE projno='$projectnumber'";

I've tried a view variables - by that I mean different ways of writing it, but it doesn't work.

Any help much appreciated.

Cheers,
Chris

background:

PHP: 5.2.0
MySQL: Client API version 4.1.21

MySQLi:
Client API library version 4.1.21
Client API header version 4.1.21

bradgrafelman

Are you getting any errors? Is the query failing and, if so, did you check for MySQL errors?

christillis

bradgrafelman;10880600 wrote:
Are you getting any errors? Is the query failing and, if so, did you check for MySQL errors?

Thanks for your promptness brad. Ah, so it's not how I've written it then, doh!

I'm not getting an error, the page just blanks. It's an html form to a php file with an html 'wrapper'.

The rest of the code is like this:

$mysqli = mysqli_connect("localhost", "username", "password", "database");

$sql = "SELECT projno, description FROM project_workdone WHERE projno='$projectnumber'";

$res = mysqli_query($mysqli, $sql);

    while ($record = mysqli_fetch_assoc($res)) {

    while (list($fieldname, $fieldvalue) = each ($record)) {

    echo $fieldname.": <b>".$fieldvalue."</b><br>";
}
echo "<br>";
}
?>

Hope that helps you to help me 🙂

bradgrafelman

Try echo'ing out $sql, copy it into either the CLI or some GUI such as phpMyAdmin, and see if your query actually produces any results.

christillis

I get this:

SELECT projno, description FROM project_workdone WHERE projno=''

The strange thing is that the end quotes are actually two apostrophes ('') ?

bradgrafelman

And can you show us where the $projectnumber variable is defined?

christillis

Morning Brad, certainly, anything to help:

The search page is html, the '0702searchbyname.php' file is correctly named and within the same directory.

<html>
<head>
<title> Widget project Search</title>
</head>
<body>
<h2>Search for project entries</h2>

<br>
<FORM ACTION="0702searchbyname.php" METHOD="POST">
Please enter the project number for recent jobsheet inserts:
<br>
<INPUT NAME="[COLOR="Red"]projectnumber[/COLOR]" TYPE=TEXT>
<br>
<INPUT TYPE=SUBMIT VALUE="Search">
</form>
</body>
</html>

Thanks again!

bradgrafelman

That's not a variable definition, that's just some HTML code that's being outputted. This would be a variable definition:

$projectnumber = (isset($_POST['projectnumber']) ? $_POST['projectnumber'] : '');

In other words, it looks like your script depends on the register_globals directive which has long since been declared a security risk and deprecated. You should be using the superglobal arrays (manual page: [man]variables.superglobals[/man]) instead.

Also note that it looks like your script is vulnerable to SQL injection attacks. User-supplied data should never be placed directly into a SQL query. Instead, it should first be sanitized with a function such as mysqli_real_escape_string() (or using prepared statements instead).

christillis

Hi Brad,

thank you again for getting back to me on this one. I'll give that a go and let you know how I get on.

Thanks also for the headsup on the security risk, very very much appreciated. I'll follow the superglobals link and read up.

Cheers!🙂
Chris