-1

I am trying to display data from a mySQL database table from with PHP code and am connecting successfully but am not returning any data.

I have tried several query styles, including mysql (unimproved) and get the same result: A blank area after the "Connected Successfully" echo.

I'd like to note that I used the exact same code to connect to a locally hosted database and was able to return data in a table. Are there mySQL permissions that may cause this or could be be differences between mySQL or php versions?

$servername = "server";
$username = "dbUser";
$password = "dbPass";
$datbase = "dbName"; 

$conn = mysqli_connect($servername, $username, $password, $database);

if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
} 
echo "Connected successfully";

$result = mysqli_query($conn,"SELECT * FROM tableName ORDER BY selectedColumn");
$all_property = array();  

echo '<table class="data-table">
        <tr class="data-heading">';  


while ($property = mysqli_fetch_field($result)) {
    echo '<td>' . $property->name . '</td>';  
    array_push($all_property, $property->name);  
}
echo '</tr>'; //end tr tag

while ($row = mysqli_fetch_array($result)) {
    echo "<tr>";
    foreach ($all_property as $item) {
        echo '<td>' . $row[$item] . '</td>'; //get items using property     value
    }
    echo '</tr>';
}
echo "</table>";

Fatal error: Uncaught exception 'mysqli_sql_exception' with message 'Unknown column 'Station' in 'order clause''

in /var/www/html/wp-content/plugins/insert-php/insert_php.php(4‌​8) : eval()'d code:22 Stack trace: #0 /var/www/html/wp-content/plugins/insert-php/insert_php.php(4‌​8) : eval()'d code(22): mysqli_query(Object(mysqli), 'SELECT * FROM h...')

1 /var/www/html/wp-content/plugins/insert-php/insert_php.php(4‌​8): eval()

2 [internal function]: will_bontrager_insert_php('\r\n\r...')

3 /var/www/html/wp-includes/class-wp-hook.php(298): call_user_func_array('will_bontrager_...', Array)

4 /var/www/html/wp-includes/plugin.php(203): WP_Hook->apply_filters('\r\n\r...', Array)

5 /var/www/html/wp-includes/post-template.php(240): apply_filters('the_content', '\r\n\r...')

RiggsFolly
  • 93,638
  • 21
  • 103
  • 149
user2267031
  • 1
  • 4
  • Elaborate on "a blank area". Is the table structure still emitted to the page, but simply without rows? Are there any errors in the PHP logs? Does `mysqli_error($conn)` reveal anything? – David Jul 19 '17 at 14:33
  • You read the mysql result twice. That is not possible i think. – Jens Jul 19 '17 at 14:33
  • Have you checked your error logs? – Jay Blanchard Jul 19 '17 at 14:35
  • Do you have a table called `tablename` and does it have a column called `selectedColumn` This all looks a bit too generic to work! – RiggsFolly Jul 19 '17 at 14:46
  • Add `ini_set('display_errors', 1); ini_set('log_errors',1); error_reporting(E_ALL); mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);` to the top of your script. This will force any `mysqli_` errors to generate an Exception that you can see on the browser and other errors will also be visible on your browser. – RiggsFolly Jul 19 '17 at 14:47
  • @Davd there is not data and no table structure. It is just white space. – user2267031 Jul 19 '17 at 14:57
  • @RiggsFollyI changed the names for privacy. I am using the correct credentials and table/column names. I'll chk again, but thanks. – user2267031 Jul 19 '17 at 14:58
  • Its not necessary to obfiscate stuff like table names and column names. – RiggsFolly Jul 19 '17 at 15:00
  • Shoudl we assume you have all the usual HTML elements that make up a well formaed page structure? – RiggsFolly Jul 19 '17 at 15:01
  • @David I took the html out for conciseness. This code works perfectly when I use it to access a different database. I just tried to access another database on the same server (with the html) and it works. What could be different between the two databases that would do this? The one that does not work is 14,000 rows. Also, I am using the PHP within WP. – user2267031 Jul 19 '17 at 15:02
  • @RiggsFolly - I entered the code as you suggested and received the following errors: Fatal error: Uncaught exception 'mysqli_sql_exception' with message 'Unknown column 'Station' in 'order clause'' in /var/www/html/wp-content/plugins/insert-php/insert_php.php(48) : eval()'d code:22 Stack trace: #0 /var/www/html/wp-content/plugins/insert-php/insert_php.php(48) : eval()'d code(22): mysqli_query(Object(mysqli), 'SELECT * FROM h...') #1 /var/www/html/wp-content/plugins/insert-php/insert_php.php(48): eval() #2 [internal function]: – user2267031 Jul 19 '17 at 15:08
  • will_bontrager_insert_php('\r\n\r...') #3 /var/www/html/wp-includes/class-wp-hook.php(298): call_user_func_array('will_bontrager_...', Array) #4 /var/www/html/wp-includes/plugin.php(203): WP_Hook->apply_filters('\r\n\r...', Array) #5 /var/www/html/wp-includes/post-template.php(240): apply_filters('the_content', '\r\n\r...') – user2267031 Jul 19 '17 at 15:09
  • **Unknown column 'Station' in 'order clause''** Should be fairly easy to fix..... Now you are seeing the errors **you have to read them** – RiggsFolly Jul 19 '17 at 15:10
  • Small Note: Nobody can read code or error messages in a comment. Add them to your question in future, using the Edit link – RiggsFolly Jul 19 '17 at 15:12
  • @RiggsFolly OK - I removed the ORDER by command and now the only error I get is: Fatal error: Maximum execution time of 30 seconds exceeded in /var/www/html/wp-includes/formatting.php on line 475 – user2267031 Jul 19 '17 at 15:13
  • Well I guess there must be a lot more code you are not showing us. – RiggsFolly Jul 19 '17 at 15:14
  • https://stackoverflow.com/questions/16171132/how-to-increase-maximum-execution-time-in-php – RiggsFolly Jul 19 '17 at 15:15

1 Answers1

0

Thanks all for your help.

After reading the errors I found the table was too large to return on the WP using. In order to make sure I could correctly connect and pull data, I added the following line to the top of the code:

ini_set("max_execution_time", 60);

I also could increase the memory limit in the formatting.php file but I don't need to because I am going to use a different method to display the larger data sets.

user2267031
  • 1
  • 4