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
– MSadura, Commented Jan 13, 2014 at 11:32
Add or die(mysqli_error($connection) after your mysqli_query statement & check the error. — Rikesh
– Rikesh, Commented Jan 13, 2014 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
– Ryan, Commented Jan 13, 2014 at 11:33

Strawberry · Accepted Answer · 2014-01-13 11:34:40Z

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, 2014 at 11:34

Strawberry

34k14 gold badges43 silver badges57 bronze badges

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

2 Comments

d.abyss Over a year ago

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?)

Strawberry Over a year ago

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 :-(

madhippie · Accepted Answer · 2014-01-13 11:33:40Z

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, 2014 at 11:33

madhippie

1581 silver badge9 bronze badges

1 Comment

d.abyss Over a year ago

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.

Collectives™ on Stack Overflow

PHP MySQL select with multiple conditions

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related