I do not think there is a typo as I've tried some Hello World complexity code and still have the same issues.
There could still be a typo in the name of some MySQL entity, such as the DB name or table name. The PHP code would be valid and the SQL queries could be valid. Not too likely, but possible. One of those things you don't see because you're too close to the code and your brain filters it out. It could even be in the UR
So... what server configuration options do we have on x10? How fine-grain are permissions for a MySQL database -- table-level or database-level? [...] I see nothing about table permissions in PHPMyAdmin.
The permission grain is only what you can access in cPanel's "MySQL Databases" panel, which is database level. The permissions feature has been removed from phpMyAdmin on X10; it wouldn't be useful in any case because our SQL user accounts don't have the GRANT privilege.
Oh MY GOODNESS!!??? Now THIS will throw you for a loop... I decided to try and use my PHP code to create a test table, insert records, and select them. This worked. I then removed the creation and insert queries and re-ran it to make sure the changes had stuck (I can't remember if you had to call something special to make such changes stick)
Only if you're using transactions without autocommit, in which case you need to issue a
COMMIT statement when done updating.
and I still got the correct results on my web page. HOWEVER, when I went to PHPMyAdmin, low-and-behold the table did not exist as far as PHPMyAdmin was concerned. I could create it again, drop it, and insert/delete records in PHPMyAdmin and this did NOTHING to my page. ?!?!?
That is very, very odd. Which server are you on? The following script performs the actions you described (creating a table, adding rows, fetching rows):
PHP:
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<html>
<head>
<title>empty result test</title>
<style type="text/css">
</style>
</head>
<body>
<h1>empty result?</h1>
<pre>
<?php
/* correct the values for the following three variables to get this script to run properly */
$database='_test';
$user= '_';
$password = '';
$table='vars';
$createVars=<<<EOF
CREATE TABLE IF NOT EXISTS $table (
name VARCHAR(16) PRIMARY KEY,
value VARCHAR(64),
type enum('num', 'str', 'list', 'dict') default 'str'
);
EOF;
$dbConn = new mysqli('localhost', $user, $password);
$dbConn->select_db($database);
if ($dbConn->query($createVars)) {
} else {
echo "Couldn't create table $table: {$dbConn->error}<br/>\n";
}
if (!$dbConn->query("INSERT INTO vars (name, value) VALUES ('foo', 'bar') ON DUPLICATE KEY UPDATE value='bar'")) {
echo "Couldn't insert table $table: {$dbConn->error}<br/>\n";
}
if (!$dbConn->query("INSERT INTO vars (name, value) VALUES ('bam', 'bug-AWWK!') ON DUPLICATE KEY UPDATE value='bug-AWWK!' ")) {
echo "Couldn't insert table $table: {$dbConn->error}<br/>\n";
}
$s_result = $dbConn->query("SELECT * FROM $table WHERE name='foo'");
if ($s_result) {
echo "Result: ", $s_result->num_rows, " rows.\n";
$s_array = array();
while ($row = mysqli_fetch_assoc($s_result)) {
$s_array[] = $row;
}
var_dump($s_array);
} else {
echo "No result for <code>$database.$table</code>.";
}
?>
</pre>
</body>
</html>
I tested it on Lotus and all changes the script makes are visible in phpMyAdmin. Try it on your host and see if table
vars is visible from phpMyAdmin.
Another thing to try is to flush your original table within phpMyAdmin after adding rows (Server->Database->Table, Operations tab). As this shouldn't be necessary, I doubt this will help.
The last thing to try is dropping & recreating the database, tables and user.
Most likely you'll need to open a support ticket on this one.
The only thing I can figure is that SOMEHOW the evil gnomeish gremlins of serverland have created an alternate database with the same name, also available from localhost.
In MySQL, a database is stored as a
folder of the same name (except that invalid filename characters are translated to character triples). I suppose if the MySQL name-to-filename translation were sometimes performed improperly, you'd wind up with multiple directories for a given database name, but this would cause all sorts of problems. I really don't see how this could happen without getting all sorts of other errors.
If PHP code directs mysql_connect to 'localhost', it should be irrelevant whether or not I have MySQL installed on my local machine, correct? I mean, it has a different DB name, different users, different passwords, and PHP is executed server-side... I am really at a loss though. I'm not sure I've ever been more confused.
Correct. 'localhost' maps to the IPv6 address ::1 or IPv4 address 127.0.0.1, which is assigned to the loopback interface ("lo0" on Unix systems). The loopback device can't access the network. Furthermore, the MySQL driver will use Unix sockets rather than TCP/IP when it sees a server of "localhost" or "localhost:3306", which also never touches the network. The only way the script will access the MySQL server on your local machine is if the script is also run on your local machine (such as when testing the script on a local development server).
:biggrin: