Need Help with a Query

[noise]

Hi

First of all I'm very new with php and mysql queries and would appreciate all the help i can get.

I have managed to take the following search script sample from the internet and changed a few fields to do what i want.

http://www.webreference.com/programming/php/search/index.html

By changing the following script I achieved to query the the first name and lastname and display all the other added tables I added as well. Please see my changed code below.


You may search either by first or last name
<form method="post" action="search.php?go" id="searchform">
<p>
<input type="text" name="name">
</p>
<p>
<input type="submit" name="submit" value="Search">
</p>
</form>
<?php

if(isset($_POST['submit'])){
if(isset($_GET['go'])){
if(preg_match("/[A-Z | a-z]+/", $_POST['name'])){
$name=$_POST['name'];

//connect to the database
$db=mysql_connect ("localhost", "fresheye_staff", "q23652") or die ('I cannot connect to the database because: ' . mysql_error());

//-select the database to use
$mydb=mysql_select_db("fresheye_staff");

//-query the database table
$sql="SELECT ID, FirstName, LastName FROM staff WHERE FirstName LIKE '%" . $name . "%' OR LastName LIKE '%" . $name ."%'";

//-run the query against the mysql query function
$result=mysql_query($sql);

//-count results

$numrows=mysql_num_rows($result);

echo "<p>" .$numrows . " results found for " . stripslashes($name) . "</p>";

//-create while loop and loop through result set
while($row=mysql_fetch_array($result)){

$FirstName =$row['FirstName'];
$LastName=$row['LastName'];
$ID=$row['ID'];

//-display the result of the array

echo "<ul>\n";
echo "<li>" . "<a href=\"search.php?id=$ID\">" .$FirstName . " " . $LastName . "</a></li>\n";
echo "</ul>";
}
}
else{
echo "<p>Please enter a search query</p>";
}
}
}

if(isset($_GET['by'])){
$letter=$_GET['by'];

//connect to the database
$db=mysql_connect ("localhost", "fresheye_staff", "q23652") or die ('I cannot connect to the database because: ' . mysql_error());

//-select the database to use
$mydb=mysql_select_db("fresheye_staff");

//-query the database table
$sql="SELECT ID, FirstName, LastName FROM staff WHERE FirstName LIKE '%" . $letter . "%' OR LastName LIKE '%" . $letter ."%'";


//-run the query against the mysql query function
$result=mysql_query($sql);

//-count results
$numrows=mysql_num_rows($result);

echo "<p>" .$numrows . " results found for " . $letter . "</p>";

//-create while loop and loop through result set
while($row=mysql_fetch_array($result)){

$FirstName =$row['FirstName'];
$LastName=$row['LastName'];
$ID=$row['ID'];

//-display the result of the array

echo "<ul>\n";
echo "<li>" . "<a href=\"search.php?id=$ID\">" .$FirstName . " " . $LastName . "</a></li>\n";
echo "</ul>";
}
}

if(isset($_GET['id'])){
$contactid=$_GET['id'];

//connect to the database
$db=mysql_connect ("localhost", "fresheye_staff", "q23652") or die ('I cannot connect to the database because: ' . mysql_error());

//-select the database to use
$mydb=mysql_select_db("fresheye_staff");

//-query the database table
$sql="SELECT * FROM staff WHERE ID=" . $contactid;


//-run the query against the mysql query function
$result=mysql_query($sql);

//-create while loop and loop through result set
while($row=mysql_fetch_array($result)){

$FirstName =$row['FirstName'];
$LastName=$row['LastName'];
$idnumber=$row['idnumber'];
$position=$row['position'];
$Recommended=$row['Recommended'];

//-display the result of the array

echo "<ul>\n";
echo "<li><h4>Full Name : " . $FirstName . " " . $LastName . "</h4></li>\n";
echo "<li><h4>ID Number : " . $idnumber . "</h4></li>\n";
echo "<li><h4>Position : " . $position . "</h4></li>\n";
echo "<li><h4>Recommended&nbsp; : " . $Recommended . "</h4></li>\n";
echo "</ul>";
}
}

?>

All I need to do now is change the code a little bit to quary(search) the idnumber table in the database and NOT the first name and lastname as it is currently doing.

Can anybody please help me figure this out. I will greatly appreciate it.

[Fumigator]

The name of the table you want to query is idnumber? And what conditions do you want to put on the query? In other words, how are you going to filter the results? Right now your script uses either $_POST['name'], which is a name field from a POST form in your HTML, or from $_GET['by'], which comes from either a GET form or a query string in a URL. You also query by ID, with $_GET['id'].

So, how is this new query of yours going to work?

[noise]

thank you for the reply.

Yes the table name that I want to query is idnumber, i then what the script to give me the results of the records in the database with that idnumber.

The database has the following 4 rows that needs be be displayed as the result of the idnumber query.

FirstName
LastName
idnumber
position
Recommended

If you have a look at the last part where the results show. This part of the script does show the results correct, but currently only if i search by name or surname.

The way the original script work is that you search the name or surname and your get the results from either the firstname or lastname table. It then tells you that 1 record was found. You can then click on that to view the details. It also searchs by letters,however i do not need this bit of code. So i think the part of the script that gives you first first result is not needed.

I simply need the script to query the idnumber table from the database and display the results of that field in the database.

I hope this makes sense, sorry if its not explained well, as im still learning.

Also not sure if it is really need or dont need to query by ID aswell. This is just the ID of the row in the table. This ID row auth increminates when more applicants details are added to the database.So it might be needed..

Hopefully you can help me figure this out.thanks Fumigator

Here is a sample of the database to help for if i maybe explained it wrong
http://www.fresheyes.co.za/dbsample.jpg

[Old Pedant]

Excuse me, but the image clearly shows that the *TABLE* you are querying is staff, *NOT* "idnumber". You have a *FIELD* named "idnumber" but no table of that name.

[Old Pedant]

You need to learn the right terminology.

You wrote:Yes the table name that I want to query is idnumber, i then what the script to give me the results of the records in the database with that idnumber.

The database has the following 4 rows that needs be be displayed as the result of the idnumber query.

FirstName
LastName
idnumber
position
Recommended


In fact, you SHOULD have written:

The table that I want to query is staff and the field I want to query against is idnumber. I then want the script to show me all records in the staff table with that idnumber.

The table has the following 5 fields that needs be be displayed as the result of the:

FirstName
LastName
idnumber
position
Recommended


So...this is really pretty easy. Back in a moment.

[noise]

Sorry my mistake. I am clearly wrong. The table is called staff and the field is called idnumber.

Thanks Old Pedant

I play with the code a bit a found that the following bit of code kindof works now

<form method="post" action="search_test.php?go" id="searchform">
<p>
<input type="text" name="idnumber">
</p>
<p>
<input type="submit" name="submit" value="Search">
</p>
</form>
<?php

if(isset($_POST['submit'])){
if(isset($_GET['go'])){
$name=$_POST['idnumber'];

//connect to the database
$db=mysql_connect ("localhost", "fresheye_staff", "q23652") or die ('I cannot connect to the database because: ' . mysql_error());

//-select the database to use
$mydb=mysql_select_db("fresheye_staff");

//-query the database table
$sql="SELECT idnumber FROM staff WHERE idnumber LIKE '%" . $name . "%'";

//-run the query against the mysql query function
$result=mysql_query($sql);

//-count results

$numrows=mysql_num_rows($result);

echo "<p>" .$numrows . " results found for " . stripslashes($name) . "</p>";

//-create while loop and loop through result set
while($row=mysql_fetch_array($result)){

$FirstName =$row['FirstName'];
$LastName=$row['LastName'];
$idnumber=$row['idnumber'];
$position=$row['position'];
$Recommended=$row['Recommended'];

//-display the result of the array

echo "<ul>\n";
echo "<li><h4>Full Name : " . $FirstName . " " . $LastName . "</h4></li>\n";
echo "<li><h4>ID Number : " . $idnumber . "</h4></li>\n";
echo "<li><h4>Position : " . $position . "</h4></li>\n";
echo "<li><h4>Recommended : " . $Recommended . "</h4></li>\n";
echo "</ul>";
}
}
else{
echo "<p>Please enter a search query</p>";
}
}

?>

I've taken out all the unnessary code and the search is working right now. Except now the results only show me the idnumber results and not the other fields as well. I must be leaving something simple out.

Thanks again for the help so far.

[Old Pedant]

You just need to get the id from the <form>, instead of the names, and then use that in the $sql query instead:

$id = $_POST["id"];

//-query the database table
$sql = "SELECT ID, FirstName, LastName FROM staff "
. "WHERE idnumber LIKE '%" . $id . "%'";


If you want to find only EXACT MATCHES of the id:

//-query the database table
$sql = "SELECT ID, FirstName, LastName FROM staff "
. "WHERE idnumber = ' . $id . "'";


If the idnumber field in your table only holds numbers, then you made a mistake creating it as a varchar field. But that's another discussion.

[noise]

there are two fields with id. first called id that is there only to auto_increment as far as i understand. So not sure if its needed.
The real field i want to query is the field idnumber.
(The user will submit this field into the database on another form.)

Please see if my above code can be used and how can i get it so show all the fields in that table.

[guelphdad]

When trouble shooting, remove your front end application entirely. Code only your sql and post that in the forum.

a) it makes your posts clearer

b) you will get your code working without having to wonder if you've introduced any errors from the front end application.

[noise]

Got it working as needed. All that was still wrong with the last code I posted was the SELECT part. I changed the idnumber to * and it is working perfect now.

//-query the database table
$sql="SELECT idnumber FROM staff WHERE idnumber LIKE '%" . $name . "%'";


I know its a simple script and that i didnt really know what i was talking about, but im learning and have learned alot now from your replies.

I will be visiting the forums often to learn more. Thanks guys.

Just one more think

If the idnumber field in your table only holds numbers, then you made a mistake creating it as a varchar field. But that's another discussion.

What should i change the idnumber field to. Must it be int?

[Old Pedant]

Read my code again.

It *IS* testing the idnumber field in the DB.

The name of the PHP variable DOES NOT MATTER.

The name in the $_POST must match the name of the field in your HTML <form>.

I could have coded this:

$flzzygx = $_POST["someFormFieldNameThatHoldsAnIdNumberValue"];

//-query the database table
$sql = "SELECT ID, FirstName, LastName FROM staff "
. "WHERE idnumber = '" . $flzzygx . "'";


ALL that matters is that the value gets to the SQL query correctly.

And the field you use in the WHERE *can* be, but does not *have* to be, in the list of fields you SELECT.

And it's not recommended, but you *can* use SELECT * to get *all* fields in the record. Example:

$idnumber = $_POST["idnumber"];

//-query the database table
$sql = "SELECT * staff "
. "WHERE idnumber = '" . $idnumber . "'";


Just don't fall into the trap of thinking there is some magic connection between field names in the <form> and variable names in PHP code and field names in SQL. There are NO such connections. Is it good practice to use the same names? Often. But that is YOUR choice, as the programmer.

[Old Pedant]

What should i change the idnumber field to. Must it be int?

"Must" is too strong a word. "Most likely should be" would better express it.

Clearly, if a field is only intended to hold integer values--digits only, no decimal points--then INT is the most appropriate choice.

It also is one of the fastest data types to use in queries, though indexing will affect performance more than data type.

[Old Pedant]

Just don't fall into the trap of thinking there is some magic connection between field names in the <form> and variable names in PHP code and field names in SQL. There are NO such connections. Is it good practice to use the same names? Often. But that is YOUR choice, as the programmer.

See? You have made YOUR CHOICE to use the PHP variable $name to hold an idnumber value. Personally, I find that confusing. If the variable is called $name then why doesn't it *hold* a name??? But it's not "wrong" to do that. It's just a personal preference.

[Fumigator]

See? You have made YOUR CHOICE to use the PHP variable $name to hold an idnumber value. Personally, I find that confusing. If the variable is called $name then why doesn't it *hold* a name??? But it's not "wrong" to do that. It's just a personal preference.

Just keep in mind noise, that when you come back to your script a year from now, and you need to make a change, you're going to see the variable $name and wonder why you're using a name value to query against the idnumber! :thumbsup:

IMO it's worth the effort of changing the variable name to match what it represents.

[noise]

Thanks for all the help and lessons. I know this is beginners stuff for you guys.

The reason why my code and types are wrong is because I toke a already writen sample of code that search for the name and not idnumber field. I changed the code as needed.

I now understand what you say that it doesnt need to be called name. I can now change that accordingly as i now know the simple script works.

[guelphdad]

Another tip, in the php manual look up the use of mysql_real_escape_string as you shouldn't rely on a string passed from a form without sanitizing the values passed before you insert it into your database.

[noise]

Just keep in mind noise, that when you come back to your script a year from now, and you need to make a change, you're going to see the variable $name and wonder why you're using a name value to query against the idnumber! :thumbsup:

IMO it's worth the effort of changing the variable name to match what it represents.

You right. Thanks i have now fixed that.