Jump to content

Need help finding the right query (basic)


joshm101

Recommended Posts

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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>

Link to comment
Share on other sites

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

 

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.