Build a SQL statement based on form data

AndyGB · Aug 8, 2015

I am upgrading my code from mysql to mysqli, but I have come across something I don't know how to do and Google isn't much help on this one... Basically I have a search form on my site which searches mysql based on what is completed. I build the sql query using something like this...

$query = "SELECT COUNT(profile) FROM profiles WHERE profile like '%'"; // << This will always return everything

if(isset($_POST['minage'])) {
  $query .= " AND minage >= " . $_POST['minage'];
}

if(isset($_POST['maxage'])) {
  $query .= " AND maxage >= " . $_POST['maxage'];
}

$result = mysql_query($query);

Before you jump on me, please bear in mind that is just a quick example, I wouldn't take post variables directly into SQL :/

So how do I do this in mysqli using a mysql_stmt_prepare? Also just like to add there are about 28 WHERE clauses in my query, but I have only used 2 as an example...

This would be my new query

$stmt = mysqli_prepare($sql_connect, "SELECT COUNT(profile) FROM profiles WHERE age >= ? AND age <= ?");
mysqli_stmt_bind_param($stmt, "ss", $_POST['minage'], $_POST['maxage']);
mysqli_stmt_execute($stmt);

The only way I can think of [and I don't know if this will work] is like this... ???

if (!isset($_POST['minage'])) {
$minage = '%';
}

Weedpacket · Aug 8, 2015

For this I would do something more like:

$query = "SELECT count(profile) FROM profiles";
$conditions = [];
if(isset($_POST['minage']))
{
    $conditions['minage>=?'] = ['i', $_POST['minage']];
}
....
$where_clauses = $types = $values = [];
if(!empty($conditions))
{
    foreach($conditions as $clause=>$condition)
    {
        $where_clauses[] = $clause;
        list($types[], $values[]) = $condition;
    }
    $query .= " WHERE " . join(" AND ", $where_clauses);
}
$stmt = $mysqli->prepare($query);
if(!empty($conditions))
{
    $stmt->bind_param(join($types), $values);
}

AndyGB · Aug 9, 2015

Perfect, thank you

Also, can you perhaps help me on this too?

// Check if there any messages
$stmt = mysqli_prepare($sql_connect, "SELECT p.profile, p.name, p.username, m.sentdate FROM messages m JOIN profiles p ON m.profile = p.profile WHERE (m.state = 3 OR m.state = 5) AND m.sentdate < DATE_SUB(NOW(), INTERVAL ? HOUR) AND p.blocked = 0 AND p.contacted = 1 AND p.received = 0 and m.sentfrom = ?");
mysqli_stmt_bind_param($stmt, "ss", $SendFollowUpTime, $username);
mysqli_stmt_execute($stmt);
mysqli_stmt_bind_result($stmt, $pprofile, $pname, $pusername, $msentdate);
mysqli_stmt_store_result($stmt);

if ((mysqli_stmt_num_rows($stmt) == 0)) {
    echo "No messages pending";
    die();
}


// Add follow up message
while (mysqli_stmt_fetch($stmt)) {
    // do some stuff
}

Can you see anything wrong here?

The SQL statement is correct and does pull the correct data, but when it comes to the while loop it only processes the first record.

I am confused :/

Weedpacket · Aug 10, 2015

Well, I don't use MySQL (and this question isn't related to the subject of this thread, so it should really have been asked in a different thread); but I'll question the need for mysqli_stmt_bind_result().

AndyGB · Aug 11, 2015

I have had to change the code to suite my procedural style of coding, but have come stuck again...

Firstly, I submit my form using minimumage=18 and maximumage=30. I can't understand why both return blank yet 1 of the 2 if statements is executed.

$POST['minimumage'] is set to 18
$POST['maximumage'] is set to 30

if (isset($_POST['submit'])) {

$query = "SELECT profile FROM profiles WHERE state != 9 AND blocked = 0";
$conditions = [];
$lookingfor = [];

echo $_POST['minimumage']; [B]// THIS IS BLANK[/B]
echo $_POST['maximumage'];  [B]// THIS IS BLANK[/B]

// Validate minimum age
    if ((isset($_POST['minimumage'])) and (preg_match('/^\d{2}$/', $_POST['minimumage']))) {
        $conditions['minage >= ?'] = ['i', $_POST['minimumage']];
        $lookingfor[] = "Minimum age " . $_POST['minimumage'];
[B]        // THIS DOES EXECUTE CORRECTLY AND SHOWS CORRECT AGE SET[/B]
    }

// Validate maximum age
    if ((isset($_POST['maximumage'])) and (preg_match('/^\d{2}$/', $_POST['maximumage']))) {
        $conditions['maxage <= ?'] = ['i', $_POST['maximumage']];
        $lookingfor[] = "Maximum age " . $_POST['maximumage'];
[B]        // THIS DOES NOT EXECUTE[/B]
    }

if (!empty($lookingfor)) {
    foreach ($lookingfor as $var) {
        echo "<li>" . $var . "</li>";
    }
}

$where_clauses = $types = $values = [];

if (!empty($conditions)) {
    foreach ($conditions as $clause => $condition) {
        $where_clauses[] = $clause;
        list($types[], $values[]) = $condition;
    }
    $query .= join(" AND ", $where_clauses);
}

echo $query;

$stmt = mysqli_prepare($sql_connect, $query);

if (!empty($conditions)) {
    mysqli_stmt_bind_param($stmt, $types, $values);
}
mysqli_stmt_execute($stmt);
mysqli_stmt_store_result($stmt);
mysqli_stmt_fetch($stmt);

if (mysqli_stmt_num_rows($stmt) > 0) {
    echo "Found " . mysqli_stmt_num_rows($stmt) . " profiles which match this criteria";
}
}
?>

An output of the $query is shows as:
SELECT profile FROM profiles WHERE state != 9 AND blocked = 0minage >= ?

Here is an example from my form..

                <label for="maximumage">Maximum Age:</label>
                <select id="maximumage" name="maximumage">
                    <option value="18">18</option>
                    <option value="19">19</option>
                    <option value="20">20</option>
                    <option value="21">21</option>
                    <option value="22">22</option>
                    <option value="23">23</option>
                    <option value="24">24</option>
                    <option value="25">25</option>
                    <option value="26">26</option>
                    <option value="27">27</option>
                    <option value="28">28</option>
                    <option value="29">29</option>
                    <option value="30">30</option>
                </select>

AndyGB · Aug 11, 2015

Forgot to add this...

Warning: mysqli_stmt_bind_param() expects parameter 1 to be mysqli_stmt, boolean given in /home/pof/public_html/pbot/messaging.php on line 48

Warning: mysqli_stmt_execute() expects parameter 1 to be mysqli_stmt, boolean given in /home/pof/public_html/pbot/messaging.php on line 50

Warning: mysqli_stmt_store_result() expects parameter 1 to be mysqli_stmt, boolean given in /home/pof/public_html/pbot/messaging.php on line 51

Warning: mysqli_stmt_fetch() expects parameter 1 to be mysqli_stmt, boolean given in /home/pof/public_html/pbot/messaging.php on line 52

Warning: mysqli_stmt_num_rows() expects parameter 1 to be mysqli_stmt, boolean given in /home/pof/public_html/pbot/messaging.php on line 54

Derokorian · Aug 11, 2015

WHERE state != 9 AND blocked = 0minage >= ?

This isn't valid, I imagine you want blocked = 0 AND minage >= ?

As for the errors, this is because the statement is not valid and fails to prepare. You should check for this condition before proceeding to do everything else.

AndyGB · Aug 11, 2015

I've had another look this morning with a fresh head. I've fixed the where clause by adding a $query .= " AND "; into the $conditions if statement., and I've fixed the $_POST issues.

Now I am just left with the one error...
Warning: mysqli_stmt_bind_param() expects parameter 2 to be string, array given.

if (isset($_POST['submit'])) {

$query = "SELECT profile FROM profiles WHERE state != 9 AND blocked = 0";
$conditions = [];
$lookingfor = [];

// Validate minimum age
    if ((isset($_POST['minimumage'])) and (preg_match('/^\d{2}$/', $_POST['minimumage']))) {
        $conditions['minage >= ?'] = ['i', $_POST['minimumage']];
        $lookingfor[] = "Minimum age " . $_POST['minimumage'];
    }

// Validate maximum age
    if ((isset($_POST['maximumage'])) and (preg_match('/^\d{2}$/', $_POST['maximumage']))) {
        $conditions['maxage <= ?'] = ['i', $_POST['maximumage']];
        $lookingfor[] = "Maximum age " . $_POST['maximumage'];
    }

if (!empty($lookingfor)) {
    foreach ($lookingfor as $var) {
        echo "<li>" . $var . "</li>";
    }
}

$where_clauses = $types = $values = [];

if (!empty($conditions)) {
    foreach ($conditions as $clause => $condition) {
        $where_clauses[] = $clause;
        list($types[], $values[]) = $condition;
    }
	$query .= " AND ";
    $query .= join(" AND ", $where_clauses);
}

echo $query;

$stmt = mysqli_prepare($sql_connect, $query);

if (!empty($conditions)) {
    [B]mysqli_stmt_bind_param($stmt, $types, $values);[/B]
}
mysqli_stmt_execute($stmt);
mysqli_stmt_store_result($stmt);
mysqli_stmt_fetch($stmt);

if (mysqli_stmt_num_rows($stmt) > 0) {
    echo "Found " . mysqli_stmt_num_rows($stmt) . " profiles which match this criteria";
}
}

laserlight · Aug 11, 2015

Refer to the PHP manual concerning that function and you will understand the problem described by the error message.

AndyGB · Aug 11, 2015

laserlight;11049993 wrote:
Refer to the PHP manual concerning that function and you will understand the problem described by the error message.

What a great idea, how did I not think of that :mad:

laserlight · Aug 11, 2015

Well, let's take a look at mysqli_stmt_bind_param:

bool mysqli_stmt_bind_param ( mysqli_stmt $stmt , string $types , mixed &$var1 [, mixed &$... ] )

So, we see that the second parameter expects a string argument, and to elaborate this is "a string that contains one or more characters which specify the types for the corresponding bind variables".

Next, let's refer back to the warning message:

Warning: mysqli_stmt_bind_param() expects parameter 2 to be string, array given.

Yep, the warning message is confirmed by the PHP manual, and furthermore we have a hint as to what is wrong: an array was provided as the argument instead of a string.

Now, let's refer back to your code:

$where_clauses = $types = $values = [];

if (!empty($conditions)) {
    foreach ($conditions as $clause => $condition) {
        $where_clauses[] = $clause;
        list($types[], $values[]) = $condition;
    }
    $query .= " AND ";
    $query .= join(" AND ", $where_clauses);
}

echo $query;

$stmt = mysqli_prepare($sql_connect, $query);

if (!empty($conditions)) {
    mysqli_stmt_bind_param($stmt, $types, $values);
}

Hence, we see that $types was initialised as an array, then within the foreach loop you append to $types. Consequently, $types is indeed an array, e.g., ['i', 'i', 'i'], whereas according to the PHP manual you want 'iii'. Thankfully, this is easily fixed by something like:

$types = implode('', $types);

AndyGB · Aug 11, 2015

The code Weedpacket gave me was OOP based and I was trying to convert it to Procedural.
I understood the error and why it was happening but I didn't realise the solution was so simple.

I'll give that a try anyway.

Thanks

laserlight · Aug 11, 2015

Ah, if you look back at post #2, you will see:

$stmt->bind_param(join($types), $values);

join is an alias of implode, and implode allows the arguments to be in either order, so join($types) is equivalent to implode('', $types), except that the latter is recommended as it is consistent with explode's arguments.

AndyGB · Aug 11, 2015

This mysqli is proving to be a real pain, starting to wish I had stuck with mysql_query

I have added the implode but now something else has surfaced.

$type >> Array ( [0] => i [1] => i [2] => i [3] => i )
$values >> Array ( [0] => 19 [1] => 23 [2] => 3 [3] => 3 )

The arrays look okay
The $query looks okay

Warning: mysqli_stmt_bind_param(): Number of elements in type definition string doesn't match number of bind variables in /home/pof/public_html/pbot/messaging.php on line 114

To clarify, I understand what the error means, I just don't understand why it is occurring.

laserlight · Aug 11, 2015

The problem is that you cannot just pass an array like that. You need to use say, call_user_func_array(). Perhaps it would be simpler to call mysqli_stmt_bind_param for each parameter to be bound.

AndyGB · Aug 11, 2015

laserlight;11050005 wrote:
Perhaps it would be simpler to call mysqli_stmt_bind_param for each parameter to be bound.

What exactly do you mean by this?

I thought mysqli_stmt_bind_param can only be used once?

Perhaps it would be simpler to use mysql_query hahah

laserlight · Aug 11, 2015

AndyGB wrote:
What exactly do you mean by this?

I thought mysqli_stmt_bind_param can only be used once?

Oh yes, my mistake: I was thinking of what can be done with the PDO extension. The MySQLi extension is limited in this way.

AndyGB wrote:
Perhaps it would be simpler to use mysql_query hahah

I think you meant mysqli_query, but of course you would then sacrifice the separation of query and data that parameter bindings provide.

AndyGB · Aug 11, 2015

laserlight;11050009 wrote:
I think you meant mysqli_query, but of course you would then sacrifice the separation of query and data that parameter bindings provide.

No I meant sack the MySQLi off and go back to using the old mysql_query commands :p

Can't figure this out and it's really annoying me, found varies forums call_user_func_array but haven't yet found a working solution

laserlight · Aug 11, 2015

AndyGB wrote:
No I meant sack the MySQLi off and go back to using the old mysql_query commands

That would make things very difficult when you find that that extension is simply not available

AndyGB wrote:
Can't figure this out and it's really annoying me, found varies forums call_user_func_array but haven't yet found a working solution

Perhaps you should use the PDO extension then.

AndyGB · Aug 11, 2015

That makes thing even more complicated, plus I don't want to use OOP code.

Build a SQL statement based on form data

AAndyGB

Weedpacket

AAndyGB

Weedpacket

AAndyGB

AAndyGB

DDerokorian

AAndyGB

laserlight

AAndyGB

laserlight

AAndyGB

laserlight

AAndyGB

laserlight

AAndyGB

laserlight

AAndyGB

laserlight

AAndyGB