mysql_query arguments in PHP

Question

I'm currently building my first database in MySQL with an interface written in PHP and am using the 'learn-by-doing' approach. The figure below illustrates my database. Table names are at the top, and the attribute names are as they appear in the real database. I am attempting to query the values of each of these attributes using the code seen below the table. I think there is something wrong with my mysql_query() function since I am able to observe the expected behaviour when my form is successfully submitted, but no search results are returned. Can anyone see where I'm going wrong here?

enter image description here

<form name = "search" action = "<?=$PHP_SELF?>" method = "get">
    Search for <input type = "text" name = "find" /> in
    <select name = "field">
        <option value = "Title">Title</option>
        <option value = "Description">Description</option>
        <option value = "City">Location</option>
        <option value = "Company_name">Employer</option>
    </select>
    <input type = "submit" name = "search" value = "Search" />
</form>

$query = "SELECT Title, Descrition, Company_name, City, Date_posted, Application_deadline 
                     FROM job, employer, address
                     WHERE Title = $find 
                     OR Company_name = $find 
                     OR Date_posted = $find 
                     OR Application_deadline = $find
                     AND job.employer_id_job = employer.employer_id
                     AND job.address_id_job = address.address_id";

Where is the code that inserts the form data into mysql table? you seem to be only querying, but never inserting — Roman
– Roman, Commented Mar 12, 2011 at 22:47
There is test data already inserted using MySQL Workbench. My goal right now is simply to extract data from the database. — user360907
– user360907, Commented Mar 12, 2011 at 23:05

Mark Byers · Accepted Answer · 2011-03-12 22:49:48Z

There seems to be at least four problems:

You don't have quotes around $find, i.e. WHERE Title = '$find'.
You don't seem to be using mysql_real_escape_string (or did you just omit that code in your question for brevity?)
You spelled Description incorrectly.

AND has higher precedence than OR so you probably want parentheses in your expression:

WHERE (Title = '$find' 
OR Company_name = '$find' 
OR Date_posted = '$find' 
OR Application_deadline = '$find')
AND job.employer_id_job = employer.employer_id
AND job.address_id_job = address.address_id"

I suspect that one or more of these are the reason why it's not working. However to be sure you should post more of your code and your table structure.

Another point is that you are using the old ANSI-89 join syntax. I would recommend using the newer syntax added in SQL-92 (FROM a JOIN b ON ...). This would have prevented you from making the fourth error, as well as having numerous other advantages over the older syntax.

Also try using mysql_error to find out what the exact error message is. And please include the message in your question.

Elzo Valugi · Accepted Answer · 2011-03-12 22:55:41Z

1

If you like to learn by doing then learn by doing it in PDO and bind the parameters. This is the safe and correct way to do it these days.

answered Mar 12, 2011 at 22:55

Elzo Valugi

28.1k17 gold badges97 silver badges115 bronze badges

Comments

EGL 2-101 · Accepted Answer · 2011-03-12 22:43:06Z

0

Use single quotes for values in where clause.

answered Mar 12, 2011 at 22:43

EGL 2-101

1,2759 silver badges18 bronze badges

Comments

konsolenfreddy · Accepted Answer · 2011-03-12 22:44:43Z

0

Try this one:

$fields= array('Title','Company_name', 'Date_posted','Application_deadline');
if(!in_array($_GET['field'],$fields)) die(); // do some error handling here

$query = "SELECT Title, Descrition, Company_name, City, Date_posted, Application_deadline 
                     FROM job, employer, address
                     WHERE 
                     $field = '".mysql_real_escape_string($_GET['find']) ."'
                     AND job.employer_id_job = employer.employer_id
                     AND job.address_id_job = address.address_id";

answered Mar 12, 2011 at 22:44

konsolenfreddy

9,7091 gold badge28 silver badges36 bronze badges

Comments

Clay Siefken · Accepted Answer · 2011-03-12 22:45:55Z

0

If single quotes isn't your only problem (it is certainly part of it), check the return of mysql_error().

Check the code samples here: http://www.php.net/manual/en/function.mysql-query.php

answered Mar 12, 2011 at 22:45

Clay Siefken

535 bronze badges

Comments

Bloodshot · Accepted Answer · 2011-03-13 00:06:59Z

0

I would suggest also using ezSQL to do all your query handling, it's easy to drop into your code and makes all the processing easy, just include the db info in a config file, include the classes for ezSQL in the config file, setup a global call to the class like

$db = new ez_SQL();

then in your referencin php files, just do this

global $db;
$results = $db->query("SELECT statment", ARRAY_A);

you can get ezsql from: http://justinvincent.com/ezsql

answered Mar 13, 2011 at 0:06

Bloodshot

626 bronze badges

Collectives™ on Stack Overflow

mysql_query arguments in PHP

6 Answers 6

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related