Passing array value in mysql query and get current date

[TheFilmGod]

        $result = $mysqli->query("SELECT a.announce_id, a.date, u.nick_name, u.last_name, a.published, a.title, a.text
FROM announcements_ref_network ref
  INNER JOIN announcements_details a ON
    ref.announce_id = a.announce_id
    AND ref.network_id = '$network['id']'
    AND (
      a.date >= '2010-02-01'
    )
  LEFT JOIN dummy_users u ON
    a.posted_by_id = u.user_id
ORDER BY a.date ASC
LIMIT 0, 5");

 

Problem 1)  "...AND ref.network_id = '$network['id']'.." single quotes enclosed in another single quotes. How can I pass an array value into a mysql query without assigning a new variable.

 

Problem 2) How do I make this: "... a.date >= '2010-02-01' ..." instead of reading "2010-02-01" read the current date?

 

Thank you so much!

[jcanker]

Easy fix to the quote nesting:  Make $network['id'] equal to a variable before the query:  $querynetwork = $network['id']

 

PHP and MySQL handle dates differently, so there are several ways to deal with the issue.  The best solution depends on how you're handling date functions elsewhere in the php pages.  If you're not dealing with dates before 1970, my preference is to just use the Unixtime functions that is inherent with PHP, and store it as an unsigned integer in MySQL.  That will give  you h/m/s functionality later on if you decide you need it, and date-based math calculations are much easier to perform in php.

[TheFilmGod]

Thanks for the quick and great post! I do have a follow up question: Is it better to use the timestamp or datetime field type in mysql?

[PFMaBiSmAd]

1) Put the array variable inside {} so that php can find where it starts and ends -

 

AND ref.network_id = '{$network['id']}'

 

2) See the mysql CURDATE() function.

[jcanker]

It really depends on what you intend to DO with the data stored in the mysql table--that's why I tend to prefer just creating a bigint column in mysql and storing the UNXDATE that php creates in that column.  There's no fiddling around with conversion between PHP and MySQL's different ways of storing dates, and the math to see if a date /time is before or after another one is simple--just run the comparison operator in php.

 

But again, it all depends on how you intend on using the data....