CODEWITHSUNDEEP
phpmysqlsql
d.abyss
d.abyss

Reputation: 212

PHP MySQL select with multiple conditions

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

Upvotes: 0

Views: 13027

Answers (2)

Strawberry
Strawberry

Reputation: 33935

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

Upvotes: 1

madhippie
madhippie

Reputation: 158

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.

Upvotes: 1

Related Questions