Creating a dynamic MySQL query from URL paramaters

Question

I am really trying to wrap my head around this and failing miserably. What I want to do it build a MySQL query based on the URL parameters passed by the URL. I am trying to create a re usable dynamic script that can do what it needs to do based on the URL parameter.

This is what I have come up with, and it appears that it does what it is supposed to do (no errors or anything) but nothing actually gets inserted in the database. I know somewhere I have made a dumb mistake (or thought something out wrong) so hopefully one of you guys can point me in the right direction.

Thanks!

 //List all possible variables you can expect the script to receive.

$expectedVars = array('name', 'email', 'score', 'age', 'date');

// This is used for the second part of the query (WHERE,  VALUES, ETC)

$fields = array('uName','uEmail','uScore','uAge','uDate');


// Make sure some fields are actually populated....
foreach ($expectedVars as $Var)
{
        if (!empty($_GET[$Var]))
    {
        $fields[] = sprintf("'%s' = '%s'", $Var,        mysql_real_escape_string($_GET[$Var]));
}
}

if (count($fields) > 0)
{
    // Construct the WHERE Clause
    $whereClause = "VALUES " . implode(",",$fields);

    //Create the SQL query itself
    $sql = ("INSERT INTO $mysql_table ($fields) . $whereClause "); 

echo "1"; //It worked
mysql_close($con);
}
else
{
    // Return 0 if query failed.
    echo "0";
}

?>

user1646111 · Accepted Answer · 2013-08-11 18:33:54Z

1

You missed mysql_query($sql):

if(!mysql_query($sql)){
//die(mysql_error());
}

Please consider to use PDO or My SQLi using parametrize query because mysl_* function depreciated.

answered Aug 11, 2013 at 18:33

user1646111

Sign up to request clarification or add additional context in comments.

2 Comments

Chris Over a year ago

Man I was spending so much time analyzing everything else I totally missed that, I knew it was something stupid.

AbsoluteƵERØ Over a year ago

They could very easily replace all of the mysql_ functions with mysqli_ without having a massive learning curve mid-production. PDO is better though.

ironcito · Accepted Answer · 2013-08-11 18:48:57Z

0

Your SQL is all wrong. You're using the field = value syntax for an INSERT, then you're concatenating an array as if it were a string ($fields), and you're missing a couple of parentheses around the values.

answered Aug 11, 2013 at 18:48

ironcito

8376 silver badges12 bronze badges

Comments

xeo · Accepted Answer · 2013-08-11 18:54:54Z

a couple of things: i've found for php <-> mysql its important to see what's going into mysql and experiement directly with those queries in phpmyadmin when i get stuck.

1 - in my code I output mysql_error() when the query fails or when a debug flag is set. this usually explains the sql issue in a way that can point me to a misspelled field name etc...

2 - this way i can feed that mysql query directly into phpmyadmin and tweak it until it gives me the results i want. (while i'm there i can also use explain to see if i need to optimize the table)

specifics in your code. unlike C languages sprintf is implied. here's how i'd write your code:

// List all possible variables you can expect the script to receive.
$expectedvars = array('name', 'email', 'score', 'age', 'date');

// This is used for the second part of the query (WHERE,  VALUES, ETC)
// $fields = array('uName','uEmail','uScore','uAge','uDate');

$fields = array();

// Set only the variables that were populated ...
foreach ($expectedvars as $var) {
    if (!empty($_GET[$var])) {
    $name = "u" + ucwords($var);    // convert var into mysql field names
        $fields[] = "{$name} = " . mysql_real_escape_string($_GET[$var]);
    }
}

// only set those fields which are passed in, let the rest use the mysql default
if (count($fields) > 0) {

// Create the SQL query itself
$sql = "INSERT INTO {$mysql_table} SET " . implode("," , $fields);
$ret = mysql_query($sql);
if (!$ret) {
    var_dump('query_failed: ', $sql, $ret);
    echo "0"; // Query failed
} else {
    echo "1"; // It worked
}

} else {
    // Return 0 if nothing to do
    echo "0";
}

mysql_close($con);

Collectives™ on Stack Overflow

Creating a dynamic MySQL query from URL paramaters

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related