[RESOLVED] Using php to search mysql database

[mjgdunne]

Hi im looking for some code that i could use to search a customers table, user enters first name or surname and all the customers details will be displayed. Im new to coding php.
Thanks.

[tbobker]

you just need to make a query on the database. For instance:

"select * from customers where firstname = fname"

Look at the php functions mysql_query(), mysql_fetch_array() you should be able to figure it out.

[mjgdunne]

Hi thanks for your reply i came up with this code, it doesnt seem to work, is it completly wrong? Im getting the input from a textbox called myq. Thanks.

<?php
$host="localhost"; // Host name
$username="root"; // Mysql username
$password="root"; // Mysql password
$db_name="test"; // Database name
$tbl_name="customers"; // Table name

// Connect to server and select databse.
mysql_connect("$host", "root", "root")or die("cannot connect");
mysql_select_db("$db_name")or die("cannot select DB");

$myq=$_POST['myq'];

mysql_query("SELECT firstname, surname, address, age FROM customers WHERE firstname='$myq'", mysql_real_escape_string($firstname));

$result = mysql_query($query);

mysql_select_db("test");

if (!$result) {
    $message  = 'Invalid query: ' . mysql_error() . "\n";
    $message .= 'Whole query: ' . $query;
    die($message);
}

while ($row = mysql_fetch_assoc($result)) {
    echo $row['firstname'];
    echo $row['surname'];
    echo $row['address'];
    echo $row['age'];
}

?>

[tbobker]

Ok so i wouldnt worry about mysql_real_escape_string() until you have made it work. Also have you managed to actually print out the query on the page so you can see what the actual query is?

maybe your query should just be:

$query = mysql_query("SELECT firstname, surname, address, age FROM customers WHERE firstname='$myq'");

Not sure where $firstname comes from within your query?

It also looks like $query is not appended to mysql_query() which means mysql_result() wont work

Also you seem to have selected the database mysql_select_db() twice

Are you getting any connection errors?

Check that the form is post and the name of the input field is myq?

[mjgdunne]

Hi i am just getting the error: Invalid query: Query was empty Whole query:
I have included the form i am using. Thanks.

<form name="form" action="search.php" method="post">
<input type="text" name="myq" />
<input type="submit" name="Submit" value="Search" />
</form>

I have changed the php below:

<?php
$host="localhost"; // Host name
$username="root"; // Mysql username
$password="root"; // Mysql password
$db_name="test"; // Database name
$tbl_name="customers"; // Table name

// Connect to server and select databse.
mysql_connect("$host", "root", "root")or die("cannot connect");
mysql_select_db("$db_name")or die("cannot select DB");

$myq=$_POST['myq'];
$query = mysql_query("SELECT firstname, surname, address, age FROM customers WHERE myq ='$myq'");

$result = mysql_query($query);


if (!$result) {
    $message  = 'Invalid query: ' . mysql_error() . "\n";
    $message .= 'Whole query: ' . $query;
    die($message);
}

while ($row = mysql_fetch_assoc($result)) {
    echo $row['firstname'];
    echo $row['surname'];
    echo $row['address'];
    echo $row['age'];
}

?>

[tbobker]

OK what is the field myq and is it called that in the database. You need to select it as well it you want to query it.

try adding it to the select statement as below:

SELECT firstname, surname, address, age myq FROM customers WHERE myq ='$myq'
if that fails just select everything (*) to be sure

[mjgdunne]

Sorry myq is not a field in my database, firstname is a field in the database however. I have changed the code below and added the select * statement, although i am getting an error : Invalid query: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Resource id #3' at line 1 Whole query: Resource id #3
Thanks.

$myq=$_POST['myq'];
$query = mysql_query("SELECT * FROM customers WHERE firstname ='$myq'");

$result = mysql_query($query);

[tbobker]

well make sure that the table name and firstname field are all spelt correctly. If that doesnt work just do select * from customers and just see if you can at least get it to print out all records so you know that works.

try adding another parametre to the mysl_query() and mysql_select_db() i add the following parametre dont know if it is optional

asign mysql_connect() to a variable $conn for example could be anything

$conn = mysql_connect("host", "user", "pass");
mysql_select_db("database",$conn); // notice the extra parametre

//when you do your query:

mysql_query($query,$conn);

might work.

[mjgdunne]

Hi i checked all the names and they correspond to the ones in the database, i checked the manual and 'Resource id #3' is an error with fetch, would you know of just a simple line of code to print out some results removing the if and while. Thanks for all your help.

[laserlight]

I have changed the code below and added the select * statement, although i am getting an error : Invalid query: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Resource id #3' at line 1 Whole query: Resource id #3

That's because you run the query, and then attempt to run a query on the resource result returned by the first query. What you actually want to do is something like this:

mysql_connect($host, "root", "root") or die("cannot connect");
mysql_select_db($db_name) or die("cannot select DB");

if (isset($_POST['myq'])) {
    $myq = mysql_real_escape_string($_POST['myq']);
    $result = mysql_query("SELECT * FROM customers WHERE firstname ='$myq'");

while ($row = mysql_fetch_assoc($result)) {
    // ...
}

// ...
}

i add the following parametre dont know if it is optional

It is optional though necessary if there is more than one such connection to the database.

[mjgdunne]

Thanks that all works now, is there any way of formatting the results into a table for example? And could this data be edited and saved?
Thanks

[laserlight]

is there any way of formatting the results into a table for example?

Yes. Just consider how a table is done in HTML, then use PHP to print one.

And could this data be edited and saved?

I am not sure what you mean by "this data", but the answer is probably yes.

[mjgdunne]

The search function searches and displays customer information, the data is the information held on the customer. Thanks