[SOLVED] Problem with database query

[scarlson]

Can someone check my code to see what's wrong with it?  I'm not getting an error but I am also not getting the correct result either.  I am trying to get the "id" from my login table so I can use it to INSERT that value into 2 other tables so I can relate those tables with each other.  When I checked my database to see what information was written I was give the value of "0".  It should have give the id value of 4 .

 

Here is my code:

 

<?php
session_start();

if(isset($_SESSION['myusername'])){

// logged in


}
else
{

header("location:account_setup.php"); // REDIRECT TO LOGIN PAGE

}
?>

<?php
include "dbconnect.php";

$username = $_SESSION['myusername'];

$logid = "SELECT id FROM login WHERE username = '$username' ";
$resultid = mysql_query($logid);

$sellerid = "SELECT login_id FROM seller WHERE login_id = '$resultid'";
$sellerid_result = mysql_query($sellerid);

$count = mysql_num_rows($sellerid_result);

if ($count == 1) {
echo "Count == 1, it worked";
//insert code to update form with all the data, if count is zero then it's a new user

}

else {
//need to set login_id from seller table with id from login table and also set the login_id from the items table
echo "DID NOT WORK";
mysql_query("INSERT INTO seller (login_id) VALUES ('$resultid')");
mysql_query("INSERT INTO items (login_id) VALUES ('$resultid')");


}

?>

 

Thanks,

 

Scott

[mr_mind]

I have found that in the past you cannot select only one column from the database or it will not return properly. you will get something like "Resource id #9". I have added in some error checking and fixed some mistakes. Also i assume in this that you have the mysql_connect somewhere before this in the document

 

 

<?php
session_start();
if(isset($_SESSION['myusername'])){
	//if the user is logged in continue
	include "dbconnect.php";
	$username = $_SESSION['myusername'];
	$logid_query = mysql_query("SELECT * FROM login WHERE username='$username'");
	$logid_array = mysql_fetch_array($logid_query);
	$logid = $logid_array['id'];
	$sellerid_query = mysql_query("SELECT * FROM seller WHERE login_id='$resultid'");
	$sellerid_array = mysql_fetch_array($sellerid_query);
	$sellerid_rows = mysql_num_rows($sellerid_query);
	$sellerid = $sellerid_array['login_id'];
	if ($sellerid_rows == 1) {
		//if the number returned by the query is 1 then tell them that
		print 'Count is 1, it worked';
	}
	else {
		print 'Count is ' . $sellerid_rows . ' ';
		if($sellerid_rows < 1) {
			//if the count is less than one then insert the user
			print ', inserting user<br /><ul>'
			print 'Inserting user into seller table ';
			if(mysql_query("INSERT INTO seller (login_id) VALUES ('$logid')")) {
				//if the user was inserted into seller say everything is ok
				print '<span style="color: #00FF00>[ OK ]</span><br />"';
				print 'Inserting user into items table ';
				if(mysql_query("INSERT INTO items (login_id) VALUES ('$logid')")) {
					//if the user was inserted into items say everything is ok
					print '<span style="color: #00FF00>[ OK ]</span>"';
					print '</ul> User inserted correctly';
				}
				else {
					//if the user was not inserted into items print that the script failed at this step
					print '<span style="color: #FF0000>[ Failed ]</span> Could not connect to the server server. Notify the site admin.';
					print '</ul> User not insterted correctly';
				}
			}
			else {
				//if the user was not inserted into seller print that the script failed at this step
				print '<span style="color: #FF0000>[ Failed ]</span> Could not connect to the server server. Notify the site admin.';
				print '</ul> User not insterted correctly';
			}
		}
		else {
			//if the count is not less than one we dont know what happened
			//what could have happened if you get this error is that the number is greater than one
			//or the value returned is not a number. If this happens the value can be checked by looking at the printed code above
			print '<span style="color: #FF0000>[ Failed ]</span> An unknown error occured. Notify the site admin.';
			print '</ul> User not insterted correctly';
		}
	}
}
else {
	//if the user is not logged in send them to the login page
	//i added in the message because on high traffic servers this may take a couple seconds and we dont want the user to not know whats going on
	print 'You must be logged in to view this page. Please wait while you are redirected';
	header("location:account_setup.php");
}
?>

[stuffradio]

If you select one column, that's the only column that will be displayed. So if you want to get all the columns, unless you want to do something special like, DISTINCT(field_name) just use the "*" instead.

 

mysql_query("SELECT * FROM `table_name` WHERE `field_name`='value'");

[roopurt18]

@mr_mind

I have found that in the past you cannot select only one column from the database or it will not return properly. you will get something like "Resource id #9".

You are way off the mark here.  Whenever you run a SELECT query you can choose to return a single column, two columns, 10 columns, or every possibly column that would have been returned.  The "best practice" is to only return the columns you need.  Why?  Let's say the average row size is 100 bytes and all you really want is the `id` column, which is an INTEGER.  For argument's sake, let's say an INTEGER is only 4 bytes and that your query returns 1000 records.  If you select all of the columns (when all you want is the `id`), then MySQL has to return to you a result set that is 1000 (records) * 100 (bytes per record) ~= 97 kilobytes of data.  If all you had SELECT'ed was the `id`, MySQL would return only 1000 (records) * 4 (bytes per `id`) ~= 4 kilobytes of data.  That extra 93kb from selecting everything is unnecessary overhead.

 

In addition, every single SELECT query that you send through mysql_query() will return a resource identifier.  Whether you select 1 field or all of them, printing the return variable of a SELECT query in PHP will always say "Resource id #XYZ."

$q = mysql_query('SELECT * FROM `users`');
echo $q . '<br>';

$q = mysql_query('SELECT `id` FROM `users`');
echo $q . '<br>';

Both of those will echo "Resource id #X."

 

In order to obtain your records, you always have to check the result for errors and then perform a loop to run over the returned record set.

$q = mysql_query('SELECT * FROM `users`');
print_record_set($q);

$q = mysql_query('SELECT `id` FROM `users`');
print_record_set($q);

function print_record_set($q){
  if(!$q){
    echo 'Error: ' . mysql_error();
    return;
  }
  echo 'PRINTING RESULTS...<br>';
  while($row = mysql_fetch_assoc($q)){
    echo '<pre style="text-align: left;">' . print_r($row, true) . '</pre>';
  }
}

 

For the original poster, if you look carefully at what I'm explaining and what you're doing, you'll find where you went wrong.

[scarlson]

Thanks for everyones input, I have managed to get this to work now.