[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

PHP/MySQL Debugging

Posted on 2011-10-05
9
Medium Priority
?
524 Views
Last Modified: 2012-05-12
I'm using WAMP and Dreamweaver CS5

I've got an error message on the testing server rendering E_NOTICE errors when E_NOTICE errors aren't even turned on in error reporting.

Furthermore

Warning: mysql_fetch_assoc() expects parameter 1 to be resource, boolean given in C:\wamp\www\galaxy\admin\addcity.php on line 76

Warning: mysql_num_rows() expects parameter 1 to be resource, boolean given in C:\wamp\www\galaxy\admin\addcity.php on line 77

Warning: mysql_free_result() expects parameter 1 to be resource, boolean given in C:\wamp\www\galaxy\admin\addcity.php on line 95

Notice: Undefined index: city in C:\wamp\www\galaxy\admin\addcity.php on line 100
Call Stack
0
Comment
Question by:RalphS007
  • 6
  • 3
9 Comments
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 36918379
This means that your query failed and returned FALSE.  The code snippet teaches some of the basics about MySQL, including how to visualize the queries along with the associated errors, so you can figure out what failed and what to do about it.
<?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



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


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

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




// ESCAPE AN EXTERNAL DATA FIELD FOR 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)
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>QUERY FAIL: ";
    echo "<br/>$sql <br/>";
    die($errmsg);
}
// 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);
if (!$num)
{
    echo "<br/>QUERY FOUND NO DATA: ";
    echo "<br/>$sql <br/>";
}
else
{
    echo "<br/>QUERY FOUND $num ROWS OF DATA ";
    echo "<br/>$sql <br/>";
}




// ITERATE OVER THE RESULTS SET TO SHOW WHAT WE FOUND
while ($row = mysql_fetch_assoc($res))
{
    var_dump($row);
}




// A 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)
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>QUERY FAIL: ";
    echo "<br/>$sql <br/>";
    die($errmsg);
}
// 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)
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>QUERY FAIL: ";
    echo "<br/>$sql <br/>";
    die($errmsg);
}
// 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

0
 

Author Comment

by:RalphS007
ID: 36919078
As per your suggestion I have copied and pasted your code & started the process of troubleshooting.

In line 50 of your code it says $_POST["username"]

Prior to line 50 it seems to pass inspection on your code, but when i get to line 50 it says

Notice: Undefined index: username in C:\wamp\www\galaxy\testsql.php on line 50

QUERY FAIL:
SELECT id FROM my_table WHERE username=''
1146 Table 'gwbeta_cms.my_table' doesn't exist
0
 

Author Comment

by:RalphS007
ID: 36919100
Here is the file i was working on and the file that you put in that snippet with a couple tweaks to it.
<?php require_once('../Connections/gwbeta_cms.php'); ?>
<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
{
  if (PHP_VERSION < 6) {
    $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;
  }

  $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

  switch ($theType) {
    case "text":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;    
    case "long":
    case "int":
      $theValue = ($theValue != "") ? intval($theValue) : "NULL";
      break;
    case "double":
      $theValue = ($theValue != "") ? doubleval($theValue) : "NULL";
      break;
    case "date":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;
    case "defined":
      $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
      break;
  }
  return $theValue;
}
}

mysql_select_db($database_gwbeta_cms, $gwbeta_cms);
$query_state = "SELECT location_usa_state.state_abrev FROM location_usa_state";
$state = mysql_query($query_state, $gwbeta_cms) or die(mysql_error());
$row_state = mysql_fetch_assoc($state);
$totalRows_state = mysql_num_rows($state);
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Add City</title>
</head>

<body>

<form id="addcity" name="addcity" method="post" action="addcity.php">

<?php

	$state = isset($state);
	$city = isset($city);
	$country = isset($country);

?>

<table width="200" align="center" border="0">
  <tr>
      <td>City: </td>
      <td>
      	<input name="city" type="text" />
      </td>
  </tr>
  <tr>
      <td>State: </td>
      <td>
        <label for="state"></label>
        <select name="state" id="state">
          <?php
do {  
?>
          <option value="<?php echo $row_state['state_abrev']?>"<?php if (!(strcmp($row_state['state_abrev'], strtoupper($row_state['state_abrev'])))) {echo "selected=\"selected\"";} ?>><?php echo $row_state['state_abrev']?></option>
          <?php //start debugging here
} while ($row_state = mysql_fetch_assoc($state));
  $rows = mysql_num_rows($state); //end debugging here
  if($rows > 0) {
      mysql_data_seek($state, 0);
	  $row_state = mysql_fetch_assoc($state);
  }
?>
        </select>
      </td>
  </tr>
  <tr>
    <td colspan="2" align="center"><input type="hidden" name="country" value="USA" />      <input type="submit" value="Submit" /></td>
  </tr>
</table>
</form>

</body>
</html>
<?php
mysql_free_result($state);
?>
<?php

	$insert = "INSERT INTO gwbeta_cms.location_usa_city (city ,state ,
country) VALUES ( " . $_POST['city'] . ", " . $_POST['state'] . ", " . $_POST['country'] . ");"; //debugging needed here

?>

Open in new window

<?php // RAY_mysql_example.php

// 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

//Call file that stores login credentials such as user, pass, etc for the database to use.
require 'Connections/gwbeta_cms/credentials.php';

// OPEN A CONNECTION TO THE DATABASE SERVER
if (!$db_connection = mysql_connect("$ldbh", "$ldbu", "$ldbp"))
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>NO DB CONNECTION: ";
    echo "<br/> $errmsg <br/>";
}

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




// ESCAPE AN EXTERNAL DATA FIELD FOR 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)
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>QUERY FAIL: ";
    echo "<br/>$sql <br/>";
    die($errmsg);
}
// 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);
if (!$num)
{
    echo "<br/>QUERY FOUND NO DATA: ";
    echo "<br/>$sql <br/>";
}
else
{
    echo "<br/>QUERY FOUND $num ROWS OF DATA ";
    echo "<br/>$sql <br/>";
}




// ITERATE OVER THE RESULTS SET TO SHOW WHAT WE FOUND
while ($row = mysql_fetch_assoc($res))
{
    var_dump($row);
}




// A 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)
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>QUERY FAIL: ";
    echo "<br/>$sql <br/>";
    die($errmsg);
}
// 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)
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>QUERY FAIL: ";
    echo "<br/>$sql <br/>";
    die($errmsg);
}
// 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

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:RalphS007
ID: 36921208
it's still erroring out.
0
 

Author Comment

by:RalphS007
ID: 36921485
New Error Codes:
QUERY FOUND 5 ROWS OF DATA
SELECT city FROM location_usa_city WHERE city=''

array
  'city' => string '' (length=0)

array
  'city' => string '' (length=0)

array
  'city' => string '' (length=0)

array
  'city' => string '' (length=0)

array
  'city' => string '' (length=0)


THERE ARE 8 ROWS IN THE TABLE
YOU JUST INSERTED A RECORD WITH AUTO_INCREMENT ID = 9

Open in new window

0
 

Author Comment

by:RalphS007
ID: 36925325
Does this mean that it succeeded?
0
 
LVL 111

Accepted Solution

by:
Ray Paseur earned 2000 total points
ID: 36925858
It appears that the queries succeeded.  You may not have the data you want yet, but it looks like you've got the MySQL part doing something now.

Let me recommend a good book for you.  Instead of trying to learn PHP by reading Dreamweaver code (some of the worst PHP code ever written) give yourself a little time to absorb the information from SitePoint.  Very readable with excellent examples and a code library you can download and tailor to your own needs.
http://www.sitepoint.com/books/phpmysql4/

Bet regards, ~Ray
0
 

Author Comment

by:RalphS007
ID: 36927352
Great.  You might want to consider putting the my_table, username, and id in a variable in the example you showed me when you recycle that to paste it elsewhere.

Also now that i have the connection working and know that it can find tables & insert into them, the follow up question I have for you then is:

How do I connect the code you sent me to a form like the one below?
<?php require 'processor.php'; ?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title><?php echo $st . "--" . $pt; ?></title>
<style>
	body {
		background-color: navy;
		color: #567E3A;
	}
	
	.fields {
		background-color: #567E3A;
		color: yellow;
	}
	
	.output {
		background-color: #FFFFFF;
		color: #567E3A;
	}
	
</style>
<script src="../../SpryAssets/SpryValidationSelect.js" type="text/javascript"></script>
<script src="../../SpryAssets/SpryValidationTextField.js" type="text/javascript"></script>
<link href="../../SpryAssets/SpryValidationSelect.css" rel="stylesheet" type="text/css" />
<link href="../../SpryAssets/SpryValidationTextField.css" rel="stylesheet" type="text/css" />
</head>
<body>
	
	<table>
	<tr>
	<td>
	<form action="general.php" method="post" name="choose_dept">
        <p><span id="spryselect1">
        <label for="department">Department: </label>
		<select class="fields" name="department" size="1" id="department">
		  <option selected="selected" value="">----------</option>        
		  <option value="sales">Sales</option>
		  <option value="billing">Billing</option>
		  <option value="hosting">Shared Hosting Support</option>
		  <option value="hosting">Reseller Support</option>
	  </select>
        <span class="selectRequiredMsg">Please select an item.</span></span></p>
<p>
	    <span id="sprytextfield1">
	    <label for="first_name">First Name: </label>
	    <input type="text" name="first_name" id="first_name" />
	    <span class="textfieldRequiredMsg">A value is required.</span></span></p>
	  <p><label for="last_name">Last Name:</label>
	    <input type="text" name="last_name" class="fields" id="last_name" /></p>
	  <p><label for="email">Email Address:</label>
	    <input class="fields" type="text" name="email" id="email" /></p>
	  <p><input name="" type="submit" value="Submit" /></p>
	</form>
	</td>
	</tr>
	</table>
<script type="text/javascript">
var spryselect1 = new Spry.Widget.ValidationSelect("spryselect1");
var sprytextfield1 = new Spry.Widget.ValidationTextField("sprytextfield1");
</script>
</body>
</html>

Open in new window

0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 36927554
Using forms in HTML and PHP is a completely different topic from debugging query failures.  You can learn more about PHP forms here.  If that doesn't immediately help you might want to post a new question.
http://us3.php.net/manual/en/tutorial.forms.php

This last "how do I connect the code" question shows why I think you will benefit from the SitePoint book.  There are so many moving parts to a web application that you will never come to the end of the questions without mastering several different technologies.  It's a little like asking, "how do I write German poetry?"  First you have to know some German.  The SitePoint book will not make you fluent in web development, but it's a good place to learn many of the basics.

Best of luck with your project, ~Ray
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If your site has a few sections that need to be secure when data is transmitted between the server and local computer, such as a /order/ section for ordering or /customer/ which contains customer data, etc it would of course be recommended to secure…
It is possible to boost certain documents at query time in Solr. Query time boosting can be a powerful resource for finding the most relevant and "best" content. Of course the more information you index, the more fields you will be able to use for y…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
Suggested Courses
Course of the Month19 days, 18 hours left to enroll

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question