I'm trying a simple php example to confirm data retrieval from a MySql database and I'm not seeing any data returned, but also no specific error either. Any advice for what to try next will be greatly appreciated!
I have a local MySql db as well as an internet web site instance and they are both doing the same thing ... which is ... I can connect to the mysql server, then connect to the database, but the sql result has no data.
Viewing over the respective db's I can see the table and data. I've not previously seen a failure running a valid query after connecting to the server and db.
Locally I'm using VS2012 with a php tool (DEVSENSE) and on the internet web site I've created the below php file and am accessing it via web address.
The following is the php file ... note that I've added some additional output to show the contents of some of the variables.
<body>
<form>
<?php
$mysqlserver="localhost";
$mysqlusername="user";
$mysqlpassword="mypassword";
$mysqldatabase="mydata";
$link = mysql_connect($mysqlserver, $mysqlusername, $mysqlpassword);
if (!$link) {
die('Could not connect: ' . mysql_error());
}
if ($link) {
echo '<a>MySql db link= ' . $link . '</a><br>';
$db_selected = mysql_select_db($mysqldatabase, $link);
if (!$db_selected) {
echo '<select id="Error" name="err">';
echo '<option value="Error"></option>';
echo '</select>';
die ('<a> Can\'t use database: ' . $mysqldatabase . '</a>');
}
echo '<a>database= ' . $mysqldatabase . '</a><br>';
$sql = sprintf("SELECT Location FROM `myLocations` Order by Location");
echo '<a>sql= ' . $sql . '</a><br>';
$result = mysql_query($link, $sql);
echo '<a>result= ' . $result . '</a><br>';
if (!$Result) {
echo '<label>Locations (none) </label>';
echo '<select id="Error" name="err">';
echo '<option value="Error"></option>';
echo '</select>';
die ("<a>Error: (" . mysql_errno($link) . "): " . mysql_error($link) . "</a>");
}
echo '<label>Locations</label> ';
echo '<select name="selectedLocation">';
echo '<option value=""></option>';
$num_results = mysql_num_rows($result);
for ($i=0;$i<$num_results;$i++) {
$row = mssql_fetch_array($result);
$name = $row['name'];
echo '<option value="' .$name. '">' .$name. '</option>';
}
echo '</select>';
mysql_close($link);
}
?>
</form>
</body>
Here's a sample returned result page using the above php file:
MySql db link= Resource id #2
database= mydata
sql= SELECT Location FROMmyLocationsOrder by Location
result=
Locations (none) Error: (0):
Here's the generated schema for table 'myLocations'
-- ----------------------------------------------------------------------------
-- Table mydata_mylocations
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS 'mydata'.`mylocations` (
`Id` INT(11) NOT NULL,
`Location` VARCHAR(50) NOT NULL,
`IsActive` TINYINT(1) NOT NULL DEFAULT '1',
`AsOfDate` DATE NULL DEFAULT NULL,
PRIMARY KEY (`Id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;
