Need help finding the right query (basic)

[joshm101]

Ok I am pretty new with databases, I am currently writing a PHP/MySQL program that: 

2 tables, a form with date input from a select.

I need data from both tables.

Here is my SQL Query:

    $sql = "SELECT sd.sponsor_id, sd.sp_name FROM sponsor_details as sd 
            INNER JOIN sponsor_message as sm
            on sd.sponsor_id = sm.sponsor_id 
            WHERE message_date = 'Whatever the date from the form input'"; This is where the problem is. I don't know how to get a form data match with an SQL record.

 

Any help would be much appreciated thanks.

[requinix]

If message_date is a DATE then the string you need should be a YYYY-MM-DD formatted value. However you get that. You haven't shown that part.

If message_date is not a DATE, or at least a DATETIME or maybe TIMESTAMP, then you have to fix that first.

[joshm101]

3 hours ago, requinix said:

If message_date is a DATE then the string you need should be a YYYY-MM-DD formatted value. However you get that. You haven't shown that part.

If message_date is not a DATE, or at least a DATETIME or maybe TIMESTAMP, then you have to fix that first.

This is what I have:

<select name="message_date" id="message_date">
                                    <option value="0">Select a Date</option>
                                    <?php
                                    
                                        date_default_timezone_set('Australia/Melbourne');
                                        $sunday= date("N");
                                        $sunday=7 - $sunday;
                                        $sunday_increment = new DateInterval('P'.$sunday .'D');
                                        
                                        $sevendays_increment = new DateInterval('P7D');
                                        $d0 =new DateTime();
                                        $d0->add($sunday_increment);
                                        for($i=date('W');$i<53;$i++)
                                            {
                                                $sundate= $d0->format("Y-m-d");
                                                $d0->add($sevendays_increment);                                            
                                                print "<option value='$sundate'>$sundate</option>";
                                            }                                                                                        
                                    ?>
                                </select>

[Barand]

If your db date fields are DATETIME or TIMESTAMP then they will not match your $message_date because of the time values.

IE "2019-06-09" <> "2019-06-09 10:30:57"

You need to extract the date portions

... WHERE DATE(message_date) = ?

Also , to make the date calculations easier

echo "<select>";                                    

$d1 = new DateTime('next sunday');
$d2 = new DateTime("last sunday of december");
$d2->modify('+1 day');
$sevendays = new DateInterval('P7D');
$dp = new DatePeriod($d1, $sevendays, $d2);
foreach ($dp as $d) {
    echo '<option value="' . $d->format('Y-m-d').'">' . $d->format('M d, Y') . '</option>';
}
                                                                                        
echo "</select>";