PHP interface to query MySQL problem

stilllearning · Joined: 02 Mar 2010 Posts: 4

Hi

I am trying to create a search in mysql using php for the UI. Basically my website has 11 fields. Any of these fields can be filled out, but then I need to pass the completed fields on to query mysql while the blank fields are excluded. For e.g. I have customername, customerlastname, StreetAddress, PhoneNumber, CustID, DOB etc etc. When I fill out customername and StreetAddress, I need the script to query mysql with only these values.

Obviously this is just 1 example. Sometimes more fields can be filled out and sometimes fewer fields can be filled out. The point is the mysql query must only use the filled out fields and not the empty fields.

I am still too new with PHP to know how to do this and will appreciate any help.

TIA
willemvw

lostboy · Joined: 02 May 2004 Posts: 6059 Location: toronto, canada

There is an [url=http://www.weberdev.com/get_example-4236.html]example with code here[/url]

stilllearning · Joined: 02 Mar 2010 Posts: 4

Hi Lostboy

Thank you for your help with this. I have managed to get the script working beautifully.

I am now onto my next (and luckily last) problem with php. I am trying to use the query script you posted to turn it into a delete script. HOwever I keep getting

"Can't connect because 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 'WHERE CustomerName LIKE 'Test 2' ''' at line 1" I am a little lost and would appreciate some help please.

The line I am changing is if ((!empty($_POST['CustomerName']))&&($_POST['CustomerName'] != 'all'))
{
$sql .= " WHERE CustomerName LIKE '". addslashes($_POST['CustomerName'])."' '$CustomerName' ";
}

From your script you had "cuenta" where I have CustomerName. From the "idclase" I have taken all that out of my delete script as I only need to select the Customer Name to delete a record from the DB.

TIA

stilllearning · Joined: 02 Mar 2010 Posts: 4

Just a quick update:

I changed the WHERE to and in the code I posted below and am now getting no "records found!" This is a step forward although it's still not working the way it should.

lostboy · Joined: 02 May 2004 Posts: 6059 Location: toronto, canada

Never use customerName as a key, what if you have 2 John Smiths? Each record should have a unique primary key (autonumber, hash, whatever) but it needs to be unique so that you don't kill incorrect records.

That said, a simple way to test your sql is to echo it out onto the page and then copy it to phpmyadmin or another gui db tool. I think in this case, you will have a funky sql because of the post and the customerName variable

[code:1]

if ((!empty($_POST['CustomerName']))&&($_POST['CustomerName'] != 'all'))
{
$sql .= " WHERE CustomerName LIKE '". addslashes($_POST['CustomerName'])."' ";
}

[/code:1] would be more like what you need

stilllearning · Joined: 02 Mar 2010 Posts: 4

Hi Lostboy

I have been trying the code change you posted to delete a field in MySQL, but so far have had no success. Basically my current code looks like this:

function search()
{
$sql = "SELECT * FROM Circuits "; // WHERE CustomerName LIKE '$CustomerName'";
if ((!empty($_POST['CustomerName']))&&($_POST['CustomerName'] != 'all'))
{
$sql .= " WHERE CustomerName LIKE '". addslashes($_POST['$CustomerName'])."' ";
}
$result = conn($sql);

if (!$result){ die("No results due to database error.<br>".mysql_error()); }

if (mysql_num_rows($result)==0)
{
echo "No Results found!";
}else{

echo "Deleting the entry ...</br>";

mysql_query("DELETE FROM Circuits WHERE CustomerName = '$sql'");

echo "Entry DELETED!!!!";

}

The above gives me no records found, but the php code shows me in the dropdown list the name I want to delete is there. Same when I do a manual search in MySQL, the name is there.

If I change the line
$sql = "SELECT * FROM Circuits "; // WHERE CustomerName LIKE '$CustomerName'";
to
$sql = "SELECT * FROM Circuits WHERE CustomerName LIKE '$CustomerName'";
ie take out the // I get an error in MySQL code which makes sense as there is a line further down ($sql .= " WHERE CustomerName LIKE '")
which duplicates the first line.

I then went on to change the $sql statements in this function to $sql1 as I thought maybe the $sql variables could be public and thus interfere with each other. (This is just a noob thinking aloud, so please don't laugh ok?) I then get the error "Can't connect cause Query is empty." I have been scratching my head and re-reading my php code numerous times, but for the life of me I cannot seem to get this solved.

Please can you help me?

TIA

vipin kumar · Joined: 05 Apr 2010 Posts: 26

I am also facing this problem.

lostboy · Joined: 02 May 2004 Posts: 6059 Location: toronto, canada

break stuff into smaller functions

[code:1]
if(isset($POST['submit'])){
$id = search();
if($id){ delete($id); }

}

function delete($id){
echo "Deleting the entry ...<br />";

mysql_query("DELETE FROM Circuits WHERE CustomerName = '$sql'");

echo "Entry DELETED!!!!";

}//end function

function search()
{
$sql = "SELECT * FROM Circuits "; // WHERE CustomerName LIKE '$CustomerName'";
if ((!empty($_POST['CustomerName']))&&($_POST['CustomerName'] != 'all'))
{
$sql .= " WHERE CustomerName LIKE '". addslashes($_POST['$CustomerName'])."' ";
}
$result = conn($sql);

if (!$result){ die("No results due to database error.<br />".mysql_error()); }

if (mysql_num_rows($result)==0)
{
echo "No Results found!";
}else{

//question: is there a possibility of having more than one record here?
//assume no
$record_id = mysql_result($result, 0, 'row_id'); //hopefully there is some unique row id

}
return $record_id;
}//end function
[/code:1]