PDO MYSQL QUERY FORMATTING

Question

I was developing a database driven website. I know the basics about php and mysql. Now there are a couple of things that i would like to ask. Like,

I have a table created in my sql named 'news' that contains following fields:

id (int)
newstitle (varchar)
newscontent (varchar)
dateadded (date)

NOTE date added only contains date.

I have created a query with php pdo which looks something like this

$query = $pdo->prepare("SELECT * FROM news ORDER BY dateadded DESC LIMIT 5");

The above query is giving me error which is something about wrong MySql syntax. What would be correct query format? What i want to do is fetch only 5 records from 'news' table in descending order sorted by Date & Time both and as date is already stored in 'dateadded' field e.g. "2019-01-03" i also want to store time along with it and query my results accordingly with date and time in descending order? And lastly i want to display the records using PHP PDO while loop.

Help will be highly appreciated.

P.S.(Sorry for bad english and if you see any error or formatting mistakes feel free to edit)

Answer 1

UPDATED

You should select database for your query

there are three method for that

1-When create PDO object

`$pdo = new PDO("mysql:server=servername;dbname=your database name","username","password");

2-run query use database

before your query run $pdo->query('use databasename);

3-add database name to your query

$query = $pdo->query("SELECT * FROM databasename.news ORDER BY dateadded DESC LIMIT 5");

Also note

You should use query method instead of prepare and also call fetchAll or fetch to get records

$query = $pdo->query("SELECT * FROM news ORDER BY dateadded DESC LIMIT 5");
$rows = $query->fetchAll(); // or you can use `fetch` and get records one by one

prepare used for parameter binding, you can use it to avoid sql injection and add user input data to query

for example

$query = $pdo->prepare("SELECT * FROM news WHERE id = ? ORDER BY dateadded DESC LIMIT 5");
$query->execute([$_GET['id']]);
$rows = $query->fetchAll(); // or you can use `fetch` and get records one by one

There are two way to set binding first using ? for parameter and second using name binding and pass assoc array to execute

Also you can use it for your query but It's increase the code by one line

$query = $pdo->prepare("SELECT * FROM news ORDER BY dateadded DESC LIMIT 5");
$query->execute(); //note: there is not any parameter binding, nothing passed
$rows = $query->fetchAll(); // or you can use `fetch` and get records one by one

http://php.net/manual/en/book.pdo.php

Answer 2

I started PHP with Procedural PHP and now i am using Object Oriented PHP Let me answer if you will able to get it,it will be pleasure for me

You can see step by step my code everything is clear :-

1.Create Connection.

2.Write query.

3.Prepare statement for your query.

4.Sanitize and Bind the parameter of the query(in case of insertion in database no need at the time of select while you are not using any variables in query).

5.Execute the query.

6.fetch the data according to your need.

           // select all query
           $query = "SELECT * FROM news ORDER BY dateadded DESC LIMIT 5"";

          // prepare query statement
          $stmt = $pdo->prepare($query);

          // execute query
          $stmt->execute();

          // Fetch data according to your need
          $num = $stmt->rowCount();

          if($num>0){

            while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {

           // extract row this will make $row['newstitle'] to just $newstitle only 

           extract($row);

           $data = array(
                "newstitle" => $newstitle,
                "newscontent " => $newscontent ,
                "dateadded" => $dateadded,

                       );

              array_push($data_arr, $);

                } //while loop closed
            } //if statement closed