0

QUERY 1:

$query = "SELECT myname, DATE_FORMAT(birthdate, '%m/%d/%Y') FROM `personal_info`";

In this query, It works and it gives me the desired format (dd/mm/yyyy), but in another query it gives me an error message.

QUERY 2 (not on the same page):

$query = "SELECT `myname`, `private_ID`, DATE_FORMAT(`birthdate`, '%m/%d/%Y'), `adress`, `phoneno`, `email`, `id` FROM `personal_info` WHERE `private_ID` = $id LIMIT 1";


$result = mysqli_query($conn, $query);

// if id exist 
// show data in inputs
if(mysqli_num_rows($result) > 0)
{
  while ($row = mysqli_fetch_array($result))
  {
    $myname= $row['myname'];
    $private_ID = $row['private_ID '];
    $birthdate= $row['birthdate'];
    $adress= $row['adress'];
    $phoneno = $row['phoneno '];
    $email= $row['email'];
    $id= $row['id'];
  }  
}

(This query #2 is used to fill fetched values from MySQL and insert it into HTML form fields.)

Notice: Undefined index: birthdate in --- on line 31 and line #31 is:

$birthdate= $row['birthdate'];

Where is my mistake here?

Peter
  • 37
  • 6
  • You need to give the generated column an alias so that you can access it easily. – jeroen Jan 21 '18 at 20:53
  • you could look at the actual output rather than just guessing, then you would see the issue –  Jan 21 '18 at 20:55
  • Note: The object-oriented interface to `mysqli` is significantly less verbose, making code easier to read and audit, and is not easily confused with the obsolete `mysql_query` interface. Before you get too invested in the procedural style it’s worth switching over. Example: `$db = new mysqli(…)` and `$db->prepare("…")` The procedural interface is an artifact from the PHP 4 era when `mysqli` API was introduced and should not be used in new code. – tadman Jan 21 '18 at 22:59
  • **WARNING**: When using `mysqli` you should be using [parameterized queries](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and [`bind_param`](http://php.net/manual/en/mysqli-stmt.bind-param.php) to add user data to your query. **DO NOT** use string interpolation or concatenation to accomplish this because you have created a severe [SQL injection bug](http://bobby-tables.com/). **NEVER** put `$_POST`, `$_GET` or **any** user data directly into a query, it can be very harmful if someone seeks to exploit your mistake. – tadman Jan 21 '18 at 22:59
  • Note that doing date formatting in the database layer is inherently problematic. It's often better to defer this to the application layer where appropriate localizations can be applied. – tadman Jan 21 '18 at 22:59
  • @tadman, when you say "NEVER put $_POST or $_GET" are you talking about lines in my code above "$myname= $row['myname'];" even if that part of the code is for fetching values from MySQL? In additional part of the code, I have prepared statements with "bind_param" for inserting values into MySQL. – Peter Jan 25 '18 at 03:17
  • Any values need escaping even if they're coming from a database already. You *need* to use `bind_param` for any data, "safe" or otherwise, or you'll have delayed-effect SQL injection bugs. If someone puts `"O'Malley"` in a name field you don't want your application to tank because you forgot to escape it when transposing from one table to another. – tadman Jan 25 '18 at 17:27

1 Answers1

0

change your query to this :

DATE_FORMAT(`birthdate`, '%m/%d/%Y') AS birthdate
sajad salimzadeh
  • 59
  • 2