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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

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

10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

RalphS007Author Commented:
it's still erroring out.
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

RalphS007Author Commented:
Does this mean that it succeeded?
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.