PHP MYSQL Select

Nathan Riley
Nathan Riley used Ask the Experts™
on
I'm not getting any results returned, can't figure out why.  The query works and returns the result in phpmyadmin.

<?php
//Connect to the Database
$host = localhost;
$username = db_user;
$password = 'pass';
$connect = mysql_connect($host,$username,$password);

//Select the Database
$db = 'db_pm';
mysql_select_db($db);

//Query the needed data
$query = "select id,userid,title,description from projects where id=1";
$result = mysql_query($query) or die('Query failed: ' . mysql_error());
?>
<link href="../assets/front.css" rel="stylesheet" type="text/css" />
<?php include("header.php");?>
    <div class="workplace">
		<div class="newprojectbox">
		<h1><?php echo($query['id']); ?><h1>
		</div>
	</div>
<?php include("footer.php");?>

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Nathan RileyFounder

Author

Commented:
Forgot to mention the result not being returned in in my code below at line 20.
Top Expert 2012

Commented:
not really a php coder but shouldn't it be $result['id'], not $query['id'] in line 20?
Dave BaldwinFixer of Problems
Most Valuable Expert 2014

Commented:
$result is not the data but the 'resource id'.  You need to use 'mysql_fetch_array' or one of the other 'mysql_fetch_...' functions to actually get the data.  See here: http://us3.php.net/manual/en/function.mysql-fetch-array.php
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

Top Expert 2012

Commented:
ah DaveBaldwin is here now so I can stop guessing PHP code and stick to MySQL =)
Most Valuable Expert 2011
Top Expert 2016
Commented:
Line 6: What is the value in $connect?  

Line 14: $result is a resource.  You need to use the resource in something like mysql_fetch_assoc() to retrieve the data from the query results set.

You have to test the return values from MySQL functions to see if they worked.  Here is a teaching example that shows how to make those tests and visualize the error conditions if the functions failed.
<?php // RAY_mysql_example.php
error_reporting(E_ALL);


// THE ABSOLUTE MINIMUM YOU MUST UNDERSTAND TO USE PHP AND MYSQL
// MAN PAGE: http://php.net/manual/en/ref.mysql.php
// MAN PAGE: http://php.net/manual/en/mysql.installation.php
// MAN PAGE: http://php.net/manual/en/function.mysql-connect.php
// MAN PAGE: http://php.net/manual/en/function.mysql-select-db.php
// MAN PAGE: http://php.net/manual/en/function.mysql-real-escape-string.php
// MAN PAGE: http://php.net/manual/en/function.mysql-query.php
// MAN PAGE: http://php.net/manual/en/function.mysql-errno.php
// MAN PAGE: http://php.net/manual/en/function.mysql-error.php
// MAN PAGE: http://php.net/manual/en/function.mysql-num-rows.php
// MAN PAGE: http://php.net/manual/en/function.mysql-fetch-assoc.php
// MAN PAGE: http://php.net/manual/en/function.mysql-fetch-array.php
// MAN PAGE: http://php.net/manual/en/function.mysql-insert-id.php



// DATABASE CONNECTION AND SELECTION VARIABLES - GET THESE FROM YOUR HOSTING COMPANY
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";
$db_user = "??";
$db_word = "??";


// OPEN A CONNECTION TO THE DATA BASE SERVER
if (!$db_connection = mysql_connect("$db_host", "$db_user", "$db_word"))
{
    $err = mysql_errno() . ' ' . mysql_error();
    echo "<br/>NO DB CONNECTION: ";
    echo "<br/> $err <br/>";
}

// SELECT THE MYSQL DATA BASE
if (!mysql_select_db($db_name, $db_connection))
{
    $err = mysql_errno() . ' ' . mysql_error();
    echo "<br/>NO DB SELECTION: ";
    echo "<br/> $err <br/>";
    die('NO DATA BASE');
}
// IF THE SCRIPT GETS THIS FAR IT CAN DO QUERIES




// ESCAPE ALL DATA FIELDS BEFORE USE IN MYSQL QUERIES
$safe_username = mysql_real_escape_string($_POST["username"]);




// CREATE AND SEND A SELECT QUERY AND TEST THE RESULTS
$sql = "SELECT id FROM my_table WHERE username='$safe_username'";
$res = mysql_query($sql);

// IF mysql_query() RETURNS FALSE, SHOW THE ERROR
if (!$res)
{
    $err = mysql_errno() . ' ' . mysql_error();
    echo "<br/>QUERY FAIL: ";
    echo "<br/> $sql <br/>";
    die($err);
}
// IF WE GET THIS FAR, THE QUERY SUCCEEDED AND WE HAVE A RESOURCE-ID IN $res SO WE CAN NOW USE $res IN OTHER MYSQL FUNCTIONS




// DETERMINE HOW MANY ROWS OF RESULTS WE GOT
$num = mysql_num_rows($res);
$fmt = number_format($num);
if (!$num)
{
    echo "<br/>QUERY FOUND NO DATA: ";
    echo "<br/> $sql <br/>";
}
else
{
    echo "<br/>QUERY FOUND $fmt ROWS OF DATA ";
    echo "<br/> $sql <br/>";
}




// ITERATE OVER THE RESULTS SET TO SHOW WHAT WE FOUND
while ($row = mysql_fetch_assoc($res))
{
    // ROW BY ROW PROCESSING IS DONE HERE
    var_dump($row);
}




// ANOTHER WAY OF DETERMINING HOW MANY ROWS WE HAVE IN A TABLE
$sql = "SELECT COUNT(*) FROM my_table";
$res = mysql_query($sql);

// IF mysql_query() RETURNS FALSE, GET THE ERROR REASONS
if (!$res)
{
    $err = mysql_errno() . ' ' . mysql_error();
    echo "<br/>QUERY FAIL: ";
    echo "<br/>$sql <br/>";
    die($err);
}
// GET THE RESULTS SET ROW IN AN ARRAY WITH A NUMERIC INDEX - POSITION ZERO IS THE COUNT
$row = mysql_fetch_array($res, MYSQL_NUM);
$num = $row[0];
$fmt = number_format($num);
echo "<br/>THERE ARE $fmt ROWS IN THE TABLE";




// MAKING AN INSERT QUERY AND TESTING THE RESULTS
$sql = "INSERT INTO my_table (username) VALUES ('$safe_username')";
$res = mysql_query($sql);

// IF mysql_query() RETURNS FALSE, GET THE ERROR REASONS
if (!$res)
{
    $err = mysql_errno() . ' ' . mysql_error();
    echo "<br/>QUERY FAIL: ";
    echo "<br/> $sql <br/>";
    die($err);
}

// GET THE AUTO_INCREMENT ID OF THE RECORD JUST INSERTED - PER THE DB CONNECTION
$id  = mysql_insert_id($db_connection);
echo "<br/>YOU JUST INSERTED A RECORD WITH AUTO_INCREMENT ID = $id";

Open in new window

HTH, ~Ray
Dave BaldwinFixer of Problems
Most Valuable Expert 2014
Commented:
Like this.  You know this will only echo '1' because that's what you have selected.

<?php
//Connect to the Database
$host = localhost;
$username = db_user;
$password = 'pass';
$connect = mysql_connect($host,$username,$password);

//Select the Database
$db = 'db_pm';
mysql_select_db($db);

//Query the needed data
$query = "select id,userid,title,description from projects where id=1";
$result = mysql_query($query) or die('Query failed: ' . mysql_error());
$rdat = mysql_fetch_array($result);
?>
<link href="../assets/front.css" rel="stylesheet" type="text/css" />
<?php include("header.php");?>
    <div class="workplace">
		<div class="newprojectbox">
		<h1><?php echo $rdat['id']; ?><h1>
		</div>
	</div>
<?php include("footer.php");?>

Open in new window

Top Expert 2012

Commented:
And Ray_Paseur too!  Gallitin, you're in good hands.  I'll leave this question now.
Dave BaldwinFixer of Problems
Most Valuable Expert 2014

Commented:
I missed some quotes.

$host = "localhost";
$username = "db_user";
$password = 'pass';

Open in new window

Most Valuable Expert 2011
Top Expert 2016

Commented:
In this query...

$query = "select id,userid,title,description from projects where id=1";

... I expect that "id=1" is an AUTO_INCREMENT KEY for the table.  Since it is almost certainly unique and since you apparently want only one row, you might want to add LIMIT 1 to the query.  Without that your query will cause a table scan, and a table scan is like dog poop - it's out of place almost anywhere you find it.
Nathan RileyFounder

Author

Commented:
Thanks guys, ended up getting it with.

<?php
//Is the user logged in?
define("_VALID_PHP", true);
  require_once("init.php");
  if (!$user->logged_in)
          redirect_to("index.php");
  $row = $user->getUserData();

//Connect to the Database
$host = 'localhost';
$username = 'db_user';
$password = 'password';
$connect = mysql_connect($host,$username,$password);
if (!$connect)  { die('Could not connect: ' . mysql_error()); }
//Select the Database
mysql_select_db("db_pm", $connect);
//Query the needed data
$result = mysql_query("SELECT id,userid,title,description FROM projects where id=1");
while($row = mysql_fetch_array($result))
  {
echo '<link href="../assets/front.css" rel="stylesheet" type="text/css" />';
include("header.php");
echo '<div class="workplace"><div class="newprojectbox"><h1>';
echo $row['title'];
echo '<h1></div></div>';
include("footer.php");
}
?>

Open in new window


Sucks you have too keep that loop open and all code in it to use it.  I don't like echoing out all the other html code. But oh well.
Most Valuable Expert 2011
Top Expert 2016

Commented:
Sucks you have too keep that loop open and all...
Are you referring to the while() ?  If so, you might want to add the LIMIT 1 clause, omit the while() and just replace lines 17 through the end with something like this...

//CONSTRUCT QUERY STRING
$sql = "SELECT id,userid,title,description FROM projects WHERE id=1 LIMIT 1";

// RUN QUERY OR SHOW ERROR INFO
$res = mysql_query($sql) or die("FAIL: $sql <br/>" . mysql_error() );

// IF QUERY FOUND NOTHING
if (!mysql_num_rows($res)) die("NONE: $sql");

// RETRIEVE RESULTS SET
$row = mysql_fetch_assoc($res);

// CREATE WEB PAGE
echo '<link href="../assets/front.css" rel="stylesheet" type="text/css" />';
include("header.php");
echo '<div class="workplace"><div class="newprojectbox"><h1>';
echo $row['title'];
echo '<h1></div></div>';
include("footer.php");

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial