'SELECT * FROM `tableA` WHERE 1'

[ScrewLooseSalad]

I want to use PHP to print the results of

 

'SELECT * FROM `tableA` WHERE 1'

 

Ithe printout in MySQL in terminal is exactly what I need displayed on the website, but I'm ony getting into interfacing MySQL and PHP and I don't know how to go about it, can anyone help? What I've tried doesn't seem to work, I can't get anything to output.

 

Can anyone point me in the right direction?

thanks

[Jessica]

There's tons of examples in the manual. Post your code and we can help.

[ScrewLooseSalad]

<?php
$db = new mysqli('localhost', 'stockuser', 'password', 'stockdatabase');
if (mysqli_connect_errno()) {
echo 'Error: Could not connect to database. Please try again later.';
echo mysqli_connect_error();
echo 'end printout';
exit;
}

// perform query
$query = "SELECT * FROM '%stocktable%' WHERE 1";
echo '<br>query set up';

$result = $db->query($query);
echo ' query entered,'

// get number of returned rows
$num_results = $result->numRows();
echo '<br>calculated rows, about to readout results<br>';

// display each returned row
for ($i=0; $i <$num_results; $i++) {
$row = $result->fetch_assoc();
echo "<p><strong>".($i+1).". PartID: ";
echo htmlspecialchars(stripslashes($row['PartID']));
echo "</strong><br />Part_Name: ";
echo stripslashes($row['Part_Name']);
echo "<br />Stock: ";
echo stripslashes($row['Stock']);
echo "<br />Supplier: ";
echo stripslashes($row['Supplier']);
echo "</p>";
}
}

echo '<br><br><br><br>script completed';
?>

 

I've identified the line "$result = $db->query($query);" to be the one preventing the script from running but I can't figure out why. I've got extra echos in there that helped me figure that out, with this line uncommented none of them show up...

Edited February 5, 2013 by ScrewLooseSalad

[Jessica]

Make sure you have error reporting turned on ,then capture MySQL errors. A blank page means you have a fatal PHP error, which you should be able to see by enabling error_reporting set to E_ALL.

 

If this is your actual code, the problem is the line after the query(). You're missing something vital.

[Maq]

Your query is invalid:

SELECT * FROM '%stocktable%' WHERE 1

 

What exactly are you trying to do?

[ScrewLooseSalad]

Make sure you have error reporting turned on ,then capture MySQL errors. A blank page means you have a fatal PHP error, which you should be able to see by enabling error_reporting set to E_ALL.

 

If this is your actual code, the problem is the line after the query(). You're missing something vital.

 

if its a fatal error, would 'error_reporting' still work? I'll try to figure out whats missing now...

 

 

Your query is invalid:

SELECT * FROM '%stocktable%' WHERE 1

 

What exactly are you trying to do?

 

It works in the terminal, I just want to print out the contents of the Table, as a lists the company stock

[Jessica]

You really should rename your table so it's just stocktable. Or even better: stock.

 

And WHERE 1 is superfluous. 

Edited February 5, 2013 by Jessica

[Jessica]

if its a fatal error, would 'error_reporting' still work? I'll try to figure out whats missing now...

 

Yes. It's an error. 

 

error_reporting

[ScrewLooseSalad]

You really should rename your table so it's just stocktable. Or even better: stock.

 

And WHERE 1 is superfluous.

 

Is that not what I called it? I thought you needed the % or it misinterpreted the ' symbol or something, one of the things I've tried to get that line to work, as it wouldn't when I first wrote it without the %

[Jessica]

A query should be:

SELECT fields FROM table.

 

You use quotes around strings, and % is a wildcard for searching.

 

However if that exact query works in command line, your table is named literally '%stocktable%'

 

Do SHOW TABLES; and see what it says.

Edited February 5, 2013 by Jessica

[ScrewLooseSalad]

Yes. It's an error.

 

error_reporting

 

I ran it and still got a blank screen, I'm just trying to figure out where it outputs a log file....

[Jessica]

Edit your php.ini to set display_errors to 1 and error_reporting to -1.

 

It's a lot easier to just read them on the screen (when in development environment) than a log file.

[Jessica]

 I told you where your error is, did you fix it?

[ScrewLooseSalad]

A query should be:

SELECT fields FROM table.

 

You use quotes around strings, and % is a wildcard for searching.

 

However if that exact query works in command line, your table is named literally '%stocktable%'

 

Do SHOW TABLES; and see what it says.

 

'%stocktable%' doesn't work in the command line, but 'stocktable' does; I misunderstood the %, I thought the PHP preprocessor wanted them as I saw it being used that way

[ScrewLooseSalad]

I told you where your error is, did you fix it?

 

hahaha, I just spotted the missing ';'

I feel like a muppet! I thought I must have been missing one but I overlooked it!

[ScrewLooseSalad]

right, its still not working, but now I'm getting a '500 Internal Server Error' message, a bit generic, but its progress...

 

I'm going to try to find that log file...

Edited February 5, 2013 by ScrewLooseSalad

[ScrewLooseSalad]

ok, the only entry relating to the blank webpage is

Invalid command 'ini_set("display_errors",

 

my .htaccess file has:

ErrorDocument 404 /404.php
ini_set("display_errors", 1);
ini_set("error_reporting ", -1);

 

Thanks for your help and patience

[Jessica]

Set them in the php.ini file. 

[Barand]

Here's a generic function for displaying query results.

 

function query2table($db, $sql)
{
   $output = "<table border='1'>\n";
   // Query the database
   $result = $db->query($sql);
   // check for errors
   if (!$result) return ("$db->error <pre>$sql</pre>");

   // get the first row and display headings
   $row = $result->fetch_assoc();
   $output .= "<tr><th>" . join('</th><th>', array_keys($row)) . "</th></tr>\n";

   // display the data
   do {
   $output .= "<tr><td>" . join('</td><td>', $row) . "</td></tr>\n";
   } while ($row = $result->fetch_assoc());
   $output .= "</table>\n";
   return $output;
}

 

Just pass it your mysqli object and query

 

$db = new mysqli(HOST, USERNAME, PASSWORD, DATABASE);

$sql = "SELECT * FROM stocktable";

echo query2table($db, $sql);

[ScrewLooseSalad]

thanks for your help guys! I've learned a lot about MySQL this week and you've been invaluable