PHP/SQL delete based on form criteria:

stevenryals · 11-12-2012, 02:56 PM

Hi all..
I have a table that i want users to be able to delete based on 2 values..
"name tag" and "cpm"
tag = varchar(25)
cpm = float

I have this form on the main page:

Code:

<form method=post action="purgestrikesearch.php">
<label>Delete Records:  Tag Name:</label>
<input size=10 type=text id=tag name=tag>
<label> Where CPM is greater than:</label>
<input type=text size=2 id=cpmval name=cpmval>
<input type=submit value="Delete">

I can't seem to wrap my head around what to do on my purgestrikesearch.php page..

obiously i'm conncting, but building the query is puzzling me..

any help would be appreciated..

thanks,
-steven

BluePanther · 11-12-2012, 03:41 PM

Quote:

Originally Posted by stevenryals

Hi all..
I have a table that i want users to be able to delete based on 2 values..
"name tag" and "cpm"
tag = varchar(25)
cpm = float

I have this form on the main page:

Code:

<form method=post action="purgestrikesearch.php">
<label>Delete Records:  Tag Name:</label>
<input size=10 type=text id=tag name=tag>
<label> Where CPM is greater than:</label>
<input type=text size=2 id=cpmval name=cpmval>
<input type=submit value="Delete">

I can't seem to wrap my head around what to do on my purgestrikesearch.php page..

obiously i'm conncting, but building the query is puzzling me..

any help would be appreciated..

thanks,
-steven

It's simple really.

Code:

DELETE FROM `table` WHERE `tag`=$verifiedInput1 AND `cpm`=$verifiedInput2

stevenryals · 11-12-2012, 03:49 PM

Sorry i know this is noob stuff.. but i'm in the learning phase here

I have:

Code:

$tagval = $_POST['tagval']; 
$cpmval = $_POST['cpmval']; 

if (!mysql_connect($db_host, $db_user, $db_pwd))
    die("Can't connect to database");

if (!mysql_select_db($database))
    die("Can't select database");

$sql = mysql_query("DELETE FROM {$table} WHERE tag = $tagval AND cpm > $cpmval" );
if (!$sql) {
    die(mysql_error());
}

it's erroring.. says: "unknown collumn 'steven' in where clause.
the tagval i'm sending is "steven-slist" so it's obviously coming from there somehow..

I thought this was because $tag was being sent, $tag was being retried by POST and then tag was the name of the collumn.. so i changed that variable to $tagval and it's still the same..

BluePanther · 11-12-2012, 03:59 PM

Quote:

Originally Posted by stevenryals

Sorry i know this is noob stuff.. but i'm in the learning phase here

I have:

Code:

$tagval = $_POST['tagval']; 
$cpmval = $_POST['cpmval']; 

if (!mysql_connect($db_host, $db_user, $db_pwd))
    die("Can't connect to database");

if (!mysql_select_db($database))
    die("Can't select database");

$tagval = mysql_real_escape_string($tagval);
$cpmval = mysql_real_escape_string($cpmval);

$sql = mysql_query("DELETE FROM {$table} WHERE tag = $tagval AND cpm > $cpmval" );
if (!$sql) {
    die(mysql_error());
}

it's erroring.. says: "unknown collumn 'steven' in where clause.
the tagval i'm sending is "steven-slist" so it's obviously coming from there somehow..

I thought this was because $tag was being sent, $tag was being retried by POST and then tag was the name of the collumn.. so i changed that variable to $tagval and it's still the same..

Notice my amendments in bold. Try that and see how it goes.

It's important, for security, to use that function every single time you put user input into an SQL query.

stevenryals · 11-12-2012, 04:03 PM

One step closer!

now i get this error:

Code:

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 'AND cpm  >' at line 1

none of that looks bad to me.. unless for some reason the 2nd variable isnt being populated..

in that case, i switched cpm > $cpmval and the tag = $tagval
the error is the same..

hmmmm.....

BluePanther · 11-12-2012, 04:40 PM

Can't see the error on my phone, but make sure the form input names match up to the POST array keys

stevenryals · 11-12-2012, 04:48 PM

Quote:

Originally Posted by BluePanther

Can't see the error on my phone, but make sure the form input names match up to the POST array keys

They do.. here's the error in text:

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 'AND cpm >' at line 1

in the input form:

<input size=10 type=text id=tagval name=tagval>
<input type=text size=2 id=cpmval name=cpmval>

in the action file:

$tagval = mysql_real_escape_string($tagval);
$cpmval = mysql_real_escape_string($cpmval);

DELETE FROM {$table} WHERE tag = $tagval AND cpm > $cpmval

Code:

$tagval = mysql_real_escape_string($tagval);
$cpmval = mysql_real_escape_string($cpmval); 

$sql = mysql_query("DELETE FROM {$table} WHERE tag = $tagval AND cpm > $cpmval " );
if (!$sql) {
    die(mysql_error());
}

BluePanther · 11-12-2012, 04:55 PM

Quote:

Originally Posted by stevenryals

They do.. here's the error in text:

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 'AND cpm >' at line 1

in the input form:

<input size=10 type=text id=tagval name=tagval>
<input type=text size=2 id=cpmval name=cpmval>

in the action file:

$tagval = mysql_real_escape_string($tagval);
$cpmval = mysql_real_escape_string($cpmval);

DELETE FROM {$table} WHERE tag = $tagval AND cpm > $cpmval

Code:

$tagval = mysql_real_escape_string($tagval);
$cpmval = mysql_real_escape_string($cpmval); 

$sql = mysql_query("DELETE FROM {$table} WHERE tag = $tagval AND cpm > $cpmval " );
if (!$sql) {
    die(mysql_error());
}

Wait until I can get on a computer.

stevenryals · 11-12-2012, 05:29 PM

Quote:

Originally Posted by BluePanther

Wait until I can get on a computer.

no worries.. thanks a ton!

stevenryals · 11-12-2012, 06:18 PM

ok..

i was in a raw command prompt and tried the same command with REAL params.. such as 'WHERE tag = testing AND cpm > 3'

i'm missing the single quotation around the value of $tval..

i think that's the problem.. but how do i do that with a variable?

BluePanther · 11-12-2012, 06:39 PM

Quote:

Originally Posted by stevenryals

ok..

i was in a raw command prompt and tried the same command with REAL params.. such as 'WHERE tag = testing AND cpm > 3'

i'm missing the single quotation around the value of $tval..

i think that's the problem.. but how do i do that with a variable?

If you notice my first reply, that contained the correct format (although I didn't know tag was going to be a string).

PHP Code:


			
$query = "DELETE FROM `table` WHERE `tag`='$verifiedInput1' AND `cpm`=$verifiedInput2";

Notice the ` around objects in the SQL (table names and column names) and ' around strings.

stevenryals · 11-12-2012, 06:47 PM

hmm.. now i get this:

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 '' at line 1

form is sending: cval and tval

my action file:

Code:

$tval = mysql_real_escape_string($tval);
$cval = mysql_real_escape_string($cval); 

$sql = mysql_query("DELETE FROM expedia WHERE `tag`='$tval' AND `cpm`=$cval" );
if (!$sql) {
    die(mysql_error());
}

my current submit form:

Code:

form method=post action="purgestrikesearch.php">
<label>Delete Records: Tag Name:</label>
<input size=15 type=text id=tval name=tval>
<label> Where CPM is greater than:</label>
<input size=2 type=text id=cval name=cval>
<input type=submit value="Delete"></b>

BluePanther · 11-12-2012, 06:51 PM

Quote:

Originally Posted by stevenryals

hmm.. now i get this:

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 '' at line 1

form is sending: cval and tval

my action file:

Code:

$tval = mysql_real_escape_string($tval);
$cval = mysql_real_escape_string($cval); 

$sql = mysql_query("DELETE FROM expedia WHERE `tag`='$tval' AND `cpm`=$cval" );
if (!$sql) {
    die(mysql_error());
}

my current submit form:

Code:

form method=post action="purgestrikesearch.php">
<label>Delete Records: Tag Name:</label>
<input size=15 type=text id=tval name=tval>
<label> Where CPM is greater than:</label>
<input size=2 type=text id=cval name=cval>
<input type=submit value="Delete"></b>

Is that your entire "action file"? Have you made sure you've updated to $_POST['tval'] and $_POST['cval']? Also, wrap your input tag attributes in HTML. You should always do that. <input size="15" type="text" id="tval" name="tval"/>.

If that still doesn't solve anything, at the top of your "action file" (after <?php obviously) put var_dump($_POST); and post here what comes out, as well as your complete "action file".

stevenryals · 11-12-2012, 07:15 PM

The variable are coming through:

array(2) { ["tval"]=> string(8) "testing3" ["cval"]=> string(2) "12" }

here is my form: (since we made a couple of small changes there)

Code:

<form method=post action="purgestrikesearch.php">
<label>Delete Records: Tag Name:</label>
<input size="15" type="text" id="tval" name="tval">
<label> Where CPM is greater than:</label>
<input size="2" type="text" id="cval" name="cval">
<input type=submit value="Delete"></b>

Here is action:

Code:

<?php

$db_host =  <defined>
$db_user =  <defined>
$db_pwd = <defined>
$database = <defined>
$table =<defined>

$tval = mysql_real_escape_string($val);
$cval = mysql_real_escape_string($cval); 

if (!mysql_connect($db_host, $db_user, $db_pwd))
    die("Can't connect to database");

if (!mysql_select_db($database))
    die("Can't select database");

$sql = mysql_query("DELETE FROM expedia WHERE `tag`='$tval' AND `cpm`=$cval" );
if (!$sql) {
    die(mysql_error());
}



mysql_free_result($result);

?>

sorry to cause such trouble on what is probably a simple mistake

stevenryals · 11-12-2012, 08:15 PM

here's an update:

i've moved my vardump through the file..

i'm now getting the variables to dump AFTER declaration..

i've added "settype($cval, "float"); to match my database..

no SQL error now.. just completely whitepaged..