PHP/MySQL Debugging

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
RalphS007Asked:
Who is Participating?
 
Ray PaseurCommented:
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
 
Ray PaseurCommented:
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
 
RalphS007Author Commented:
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
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
RalphS007Author Commented:
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
 
RalphS007Author Commented:
it's still erroring out.
0
 
RalphS007Author Commented:
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
 
RalphS007Author Commented:
Does this mean that it succeeded?
0
 
RalphS007Author Commented:
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
 
Ray PaseurCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.