PHP script returns no result

Question

I have a script where I try to get the date from my database.

The script needs to show: The date in the database is (date). Click here to continue. When I run the SQL query in phpMyAdmin, the SQL query returns the date. When I run it in my script I get no result.

Here is my script:

<?php
 $servername = "localhost";
 $username = "root";
 $password = "";
 $dbname = "db";

 // Create connection
 $conn = new mysqli($servername, $username, $password, $dbname);
 // Check connection
 if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
 } 

 $sql = "set lc_time_names = 'nl_NL';";
 $sql = "SELECT date_format(date, '%e %M %Y') AS date FROM table WHERE id='1'";
 $result = $conn->multi_query($sql);

 if ($result->num_rows > 0) {
  // output data of each row
  while($row = $result->fetch_assoc()) {
   echo "The date in the database is:";
   echo " " . $row['date'] . ". ";
   echo "Click here to continue.";
   }
  } else {
   echo "0";
  }
?>

When I run this script I get 0. When I change echo "0"; with echo " " . $row['date'] . ". "; I get a empty page.

What am I doing wrong? How can I fix this?

PHP's mysql adapter all only process the first statement in the sql argument handed over. That has security reasons to prevent sql injection attacks. That means your set statement is executed, the SELECT statement is silently dropped. — arkascha
– arkascha, Commented Jun 11, 2016 at 5:40
Ok, is there a way to run this script with: set lc_time_names = 'nl_NL';? — John
– John, Commented Jun 11, 2016 at 5:54
Run it as two separate statements in sequence. The mysql connection has a state. — arkascha
– arkascha, Commented Jun 11, 2016 at 5:55
Ah, and obviously those two assignments to the same variable $sql do not make any sense. The second string replaces the first one. — arkascha
– arkascha, Commented Jun 11, 2016 at 6:14

Fernando Rodriguez · Accepted Answer · 2016-06-11 06:19:54Z

I had another edit that isn't approved...does this work?

Just splitting your queries/variables into 2 separate ones--only only worry that first value may not persist for you.

Like so:

 <?php
 $servername = "localhost";
 $username = "root";
 $password = "";
 $dbname = "db";

 // Create connection
  $conn = new mysqli($servername, $username, $password, $dbname);
 // Check connection
  if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
 }  

$sql = "set lc_time_names = 'nl_NL'";
$sql2 = "SELECT date_format(date, '%e %M %Y') AS date FROM table WHERE            id='1'";
 $resultZ = $conn->query($sql);
 $result = $conn->query($sql2);
 if ($result->num_rows > 0) {
  // output data of each row
  while($row = $result->fetch_assoc()) {
   echo "The date in the database is:";
    echo " " . $row['date'] . ". ";
    echo "Click here to continue.";
   }
   } else {
    echo "0";
   }
    ?>

Fernando Rodriguez · Accepted Answer · 2016-06-11 05:58:25Z

2

You are using mysqli_query (which executes exactly ONE query) and thus your script is only ever getting to the set variable statement and ending at the semicolon.

Follow the advice below--the 2nd example shows using multi_query and setting the variable value just like you do in phpMyAdmin.

Try with:

<?php
 $servername = "localhost";
 $username = "root";
 $password = "";
 $dbname = "db";

 // Create connection
 $conn = new mysqli($servername, $username, $password, $dbname);
 // Check connection
 if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
 } 

 $sql = "SELECT date_format(date, '%e %M %Y') AS date FROM table WHERE id='1'";
 $result = $conn->query($sql);

 if ($result->num_rows > 0) {
  // output data of each row
  while($row = $result->fetch_assoc()) {
   echo "The date in the database is:";
   echo " " . $row['date'] . ". ";
   echo "Click here to continue.";
   }
  } else {
   echo "0";
  }
?>

Or use multi_query:

<?php
 $servername = "localhost";
 $username = "root";
 $password = "";
 $dbname = "db";

 // Create connection
 $conn = new mysqli($servername, $username, $password, $dbname);
 // Check connection
 if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
 } 

 $sql = "set lc_time_names = 'nl_NL'; SELECT date_format(date, '%e %M %Y') AS date FROM table WHERE id='1'";
 $result = $conn->multi_query($sql);

 if ($result->num_rows > 0) {
  // output data of each row
  while($row = $result->fetch_assoc()) {
   echo "The date in the database is:";
   echo " " . $row['date'] . ". ";
   echo "Click here to continue.";
   }
  } else {
   echo "0";
  }
?>

edited Jun 11, 2016 at 5:58

Fernando Rodriguez

4931 gold badge4 silver badges21 bronze badges

answered Jun 11, 2016 at 5:41

Tudor Constantin

26.9k7 gold badges54 silver badges73 bronze badges

4 Comments

John Over a year ago

Its working, but I need set lc_time_names = 'nl_NL';

John Over a year ago

It is not working again when I use $result = $conn->multi_query($sql);

John Over a year ago

@Fernando. Multi query gives me the same result

Tudor Constantin Over a year ago

@Fernando Rodriquez no problem, I am glad you helped him, because I am no longer at the computer and I couldn't update my answer. Also, please take my upvote

Freelance Developer · Accepted Answer · 2016-06-11 06:39:49Z

1

You have formatted the sql script.. So instead of writing $row['date'] just put like this $row[0]

Hope this works for you..

answered Jun 11, 2016 at 6:39

Freelance Developer

112 bronze badges

1 Comment

Fernando Rodriguez Over a year ago

FYI this would also work--but he is perfectly capable of calling the data columns by name rather than index.

Collectives™ on Stack Overflow

PHP script returns no result

3 Answers 3

Comments

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related