PHP MySQL select with multiple conditions

Question

I need to select rows from my database using multiple conditions.

Now I have done a lot of searching but was unable to find any examples similar to mine.

Please don't swarm me like sharks about SQL injections and stuff as I'm in my second week of learning PHP and what I'm making will only be used internally.

The Form:

<form action="<?php echo $_server['php_self']; ?>" method="post">
    Display results from <input name="from_date" class="src_drop" type="textarea" id="from_date">
    to <input name="to_date" class="src_date" type="textarea" id="to_date"> 
    for <select class="src_drop" name="uniqueid" type="text">

    <?php 
        $sql   = "select * from table_staff";
        $staff = mysqli_query($connection, $sql);
        while($stafflist = mysqli_fetch_array( $staff )) 
        {
            echo '<option value="' . $stafflist['uniqueid'] . '">' . $stafflist['first_name'] . " " . $stafflist['surname'] . '</option>';
        } 
    ?>
    </select>
    <input type="submit" name="submit" class="search" value="">
</form>

The SELECT:

$startDate = date("Y-m-d", strtotime($_POST['from_date']));
$endDate   = date("Y-m-d", strtotime($_POST['to_date']));
$uniqueid  = $_POST['uniqueid'];

$shiftdata = mysqli_query($connection, "SELECT * FROM table_shift 
    INNER JOIN table_staff ON table_shift.uniqueid = table_staff.uniqueid 
         WHERE shift_date BETWEEN '".$startDate."' AND '".$endDate."
           AND table_shift.uniqueid='".$uniqueid."''
      ORDER by shift_date ASC");

I'm trying to display all shifts for specific staff member between certain dates. The code works fine for selecting shifts between the dates, but breaks the connection when adding select by uniqueid. I've tried a couple different (guesses) variations of syntax e.g. AND WHERE table_shift.unqiueid... without any luck.

The Error:

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 '15'' ORDER by shift_date ASC' at line 1

Do u get any error message or your record is just not selected? — MSadura, Jan 13 '14 at 11:32
Add `or die(mysqli_error($connection)` after your mysqli_query statement & check the error. — Rikesh, Jan 13 '14 at 11:32
I know you said not to swarm you with lectures on securing your queries, but it really is good practice to start securing your software from the beginning, that way you'll never accidentally use your bad traits which you picked up at the beginning. — Ryan, Jan 13 '14 at 11:33
@Stanyer, I understand what you are saying but unfortunately I haven't been able to find any information about securing my code that makes any sense to a person who is largely writing PHP on a guesswork/trial and error basis. — d.abyss, Jan 13 '14 at 11:40
@danielsmile If you've written the code you have above, and you generally understand how it's working - you can definately start to understand why sanitation on any input to a database query is important, and how to implement it. http://stackoverflow.com/questions/11704270/comprehensive-security-against-user-input-php-mysql — Ryan, Jan 13 '14 at 11:43
@Stanyer, I understand why it's important. It's to prevent people entering SQL code into inputs on my pages I believe. I understand the concept, just haven't found a guide that can show me example code, what it does etc in a way that is understandable to me. I'll keep looking for a newbie friendly tutorial on it. — d.abyss, Jan 13 '14 at 11:53
StackOverflow has many threads/wiki's on it. http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php http://stackoverflow.com/questions/11704270/comprehensive-security-against-user-input-php-mysql — Ryan, Jan 13 '14 at 11:55
@Stanyer, I appreciate the help, but I think I'm a long way from understanding how to implement this security. I read through all the posts in the link you provided and only a small amount makes any sense. Most of it is code that I am unfamiliar with and don't understand what it does. This kind of stuff is way beyond my level. — d.abyss, Jan 13 '14 at 12:08

score 1 · Answer 1 · answered Jan 13 '14 at 11:33

1

Have you tried removing the single quotes around

..AND table_shift.uniqueid='".$uniqueid."''

so that it looks like

..AND table_shift.uniqueid=".$uniqueid."

This assumes that your uniqueid field is an integer, not a string.

answered Jan 13 '14 at 11:33

madhippie

168
1
9

I've removed the double quotes. Now when i run a query, I'm getting no error but the query is only choosing shifts between the dates and ignoring the uniqueid. – d.abyss Jan 13 '14 at 11:45

score 1 · Accepted Answer · answered Jan 13 '14 at 11:34

1

Try this.. (and I agree with others about securing your data WITH PREPARED STATEMENTS)

$startDate = date("Y-m-d", strtotime($_POST['from_date']));
$endDate = date("Y-m-d", strtotime($_POST['to_date']));
$uniqueid = $_POST['uniqueid'];
$query = "
SELECT * 
  FROM table_shift x
  JOIN table_staff y
    ON x.uniqueid = y.uniqueid 
 WHERE x.shift_date BETWEEN '$startDate' AND '$endDate'
   AND x.uniqueid = $uniqueid
 ORDER 
    BY x.shift_date ASC;
";

echo $query;
$shiftdata = mysqli_query($connection, $query) or die(mysqli_error($connection));

answered Jan 13 '14 at 11:34

Strawberry

33,750
13
40
57

Your code works great, I wasn't aware that I could use such syntax. Is this also an example of more 'secure' code? (is $query a prepared statement?) – d.abyss Jan 13 '14 at 11:56
No. But prepared statements are widely discussed elsewhere, And both object-oriented and procedural versions are available. There's some suggestion that procedural is slightly less secure - but I don't understand why :-( – Strawberry Jan 13 '14 at 11:58

PHP MySQL select with multiple conditions

2 Answers2