Php mysql fetch problem

cools_sonu · April 10, 2010, 1:13pm

Hello all.

I’m trying to retrieve information from database by using the following code. But unable to get the desired information.

Please kindly help me and advice me where I’m going wrong.



<?php
error_reporting(0);
require_once "includes/functions.php";

if(!loggedin())
	{
		header("Location: login.php");
	}
	else
	{
		require_once "includes/database.php";
		require_once "thirdparty/fpdf/fpdf.php";
		require_once "thirdparty/fpdi/fpdi.php";
		
		if(isset($_POST))
		{
			$user = $_POST['username'];
			$usql = "select id from users where username='$user'";
			$ures = query($usql);
			$urow = mysql_fetch_assoc($ures);
			$uid = $urow['id'];
		}
		else
		{
			$uid = $_SESSION['USER_ID'];
			$user = $_SESSION['USER_NAME'];
		}
		


$sql = "select * from users u, userinfo ui, employer emp where u.id=ui.uid and ui.uid = emp.uid and u.id=$uid";
		$res = query($sql);
		$row = mysql_fetch_assoc($res);

//and all rest of my code for pdf generation.

Here first I’ve created a form with post method. post contains the username.

Now I want to retrieve the userid by using that username and then by using that userid, I want to retrieve all the information from all related tables.

How could I achieve this ?

My first part of the code is correct. I mean to say that I’m able to retrieve userid, but the next part of code is not fetching the infromation from all related tables.

cools_sonu · April 10, 2010, 2:05pm

Yeah now it’s working properly.

Actually there was one table which has nothing, so it was not fetching any result. Well is there any way if one table has not got any value, then also we could be able to fetch result ?

Jake_Arkinstall · April 10, 2010, 1:34pm

First of all, I believe $_POST is always set so you need to clarify your if() condition:

if(array_key_exists('username', $_POST)){

In your second query, you should use joins rather than putting clauses in your if statement - it will save your mysql server a hell of a lot of work!

$sql = "select * from users u INNER JOIN userinfo ui ON u.id = ui.uid INNER JOIN employer emp ON ui.uid = emp.uid WHERE u.id=$uid";
        $res = query($sql);
        $row = mysql_fetch_assoc($res);

Is there a specific reason that you’re using a custom function to run the query? Does it have error handling etc?

cools_sonu · April 10, 2010, 1:41pm

Thanx Jake for replying. Where here is the basic code. along with the form too.

Actually I’ve just extracted the testing code and testing this code on a separate page.

But I’m not getting the result. I’m able to retrieve the “$uid”, but then the next part of the sql code is not performing the required request.

<?php
			require_once "includes/database.php";
			if(isset($_POST))
			{
			$user = $_POST['username'];
			$usql = "select id from users where username='".$_POST['username']."'";
			$ures = query($usql);
			$urow = mysql_fetch_assoc($ures);
			$uid = $urow['id'];
			//echo $uid;
			$sql = "select * from users u, userinfo ui, employer emp where u.id=ui.uid and ui.uid = emp.uid and u.id=$uid";
			
			$res = query($sql);
			$row = mysql_fetch_assoc($res);
			print_r($row);
			
			}
?>



<form action="" method="post">

<input type="text" name="username" />
<input type="submit" name="submit" value="Enter username" />

</form>

Jake_Arkinstall · April 10, 2010, 1:41pm

What’s the output?

cools_sonu · April 10, 2010, 1:47pm

Well I’ve posted my new modified code. And I don’t thing $_POST is set. may be I’m wrong. As in the original code I’m sending the $_POST variable into another script page.

Thanx for the help on join issue.

Actually I’m following the Apress’s Beginning with SQL Queries and following the calculus method. While as I can see most of the sitepoint’s users are recommending the algebric method, so now I’m learning that.

Yeah!! below is the code, which I’m currently using. May be it needs some more optimization and some advancement.


function query($sql)
{
	$last_query = $sql;
	$result = mysql_query($sql);
	if(!$result)
	{
		$output = "Query Failed: ". mysql_error();
		$output .= "<hr />";
		$output .= "Last Query was: ". $last_query;
		die($output);
	}
	return $result;
}

cools_sonu · April 10, 2010, 1:48pm

Notice: Undefined index: username in D:\wamp\www\spa\adminpdf.php on line 6

Notice: Undefined index: username in D:\wamp\www\spa\adminpdf.php on line 7

Query Failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘’ at line 1
Last Query was: select * from users u, userinfo ui, employer emp where u.id=ui.uid and ui.uid = emp.uid and u.id=

Jake_Arkinstall · April 10, 2010, 1:54pm

Use the code fixed I have given you above.

The first problem is that $_POST always exists. You must only attempt a login if $_POST[‘username’] exists, i.e. array_key_exists(‘username’, $_POST).

The second problem may be solved by solving the first, but I HIGHLY recommend using my version of the query. http://en.wikipedia.org/wiki/Join_(SQL)

AnthonySterling · April 10, 2010, 2:53pm

Your query is incorrect, execute it in something else (MySQL GUI, CLI, PHPMyAdmin et al) and get it to work there.

Once working, migrate into PHP.

Edit:

Whoops, I went out to the shops and replied once I had returned, therefore did not see Jakes responses.

My bad.

cools_sonu · April 10, 2010, 3:03pm

Yupp Now I’m able to get the result.

But got one more problem.

Now the issue is that, if say I’m trying to extract information from 3 tables. but if one table has not got any value, then it is not fetching any value.

So, I want to fetch the result of those tables which have got values.

But I’m unable to fetch the result, how it can be done ?

Jake_Arkinstall · April 10, 2010, 3:08pm

Fetch the results individually?

cools_sonu · April 10, 2010, 3:12pm

No no. I mean to ask

if say my code is something like.


$sql = "select * from users u INNER JOIN userinfo ui ON u.id = ui.uid INNER JOIN employer emp ON ui.uid = emp.uid WHERE u.id=$uid";
        $res = query($sql);
        $row = mysql_fetch_assoc($res);

but one of my table employer doesn’t contain any value right now(it may be filled later on). So the whole query is not able to fetch any result.

So I want it to fetch the result neglecting the empty table. How could I be able to make it ?

Jake_Arkinstall · April 10, 2010, 3:17pm

Exactly as I said - fetch the results individually

$UserSQL = "SELECT * FROM users u INNER JOIN userinfo ui ON u.id = ui.uid WHERE u.id=$uid"
$EmployeeSQL = "SELECT * FROM users u INNER JOIN employer emp ON ui.uid = emp.uid WHERE u.id=$uid"

A query like you had before, and the join I gave you - they’re both for selecting from 3 tables where records will be available. If records are in one but not another, you’d need a second query.

Waits for a database guru to contradict me

cools_sonu · April 10, 2010, 3:20pm

Jake_Arkinstall:

Exactly as I said - fetch the results individually
$UserSQL = "SELECT * FROM users u INNER JOIN userinfo ui ON u.id = ui.uid WHERE u.id=$uid"
$EmployeeSQL = "SELECT * FROM users u INNER JOIN employer emp ON ui.uid = emp.uid WHERE u.id=$uid"
A query like you had before, and the join I gave you - they’re both for selecting from 3 tables where records will be available. If records are in one but not another, you’d need a second query.

Waits for a database guru to contradict me

hmmm it means I need to put a condition here.

which I wasn’t trying to do :goof:

SpacePhoenix · April 10, 2010, 8:46pm

cools_sonu, can you show a “CREATE TABLE” for the user, userinfo and employer tables? Do you really need all fields returned for each record matched? If not then specifiy just the fields that you need, using the syntax:

table_name.field_name

cools_sonu · April 11, 2010, 2:40am

SpacePhoenix:

cools_sonu, can you show a “CREATE TABLE” for the user, userinfo and employer tables? Do you really need all fields returned for each record matched? If not then specifiy just the fields that you need, using the syntax:
table_name.field_name

ok sure.

CREATE TABLE IF NOT EXISTS users (
id int(11) NOT NULL AUTO_INCREMENT,
username varchar(255) NOT NULL,
password varchar(255) NOT NULL,
joindate datetime NOT NULL,
email varchar(255) NOT NULL,
salute char(50) NOT NULL,
sex char(20) NOT NULL,
firstname varchar(255) NOT NULL,
middle varchar(255) NOT NULL,
lastname varchar(255) NOT NULL,
lastlogin datetime NOT NULL,
PRIMARY KEY (id)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS userinfo (
id int(11) NOT NULL AUTO_INCREMENT,
uid int(11) NOT NULL,
PAN char(20) NOT NULL,
DOB date NOT NULL,
mobile bigint(11) NOT NULL,
phone char(20) NOT NULL,
father char(50) NOT NULL,
flat char(50) NOT NULL,
building varchar(255) NOT NULL,
area char(50) NOT NULL,
town char(50) NOT NULL,
state char(50) NOT NULL,
pin int(11) NOT NULL,
PRIMARY KEY (id),
KEY uid (uid,PAN)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS employer (
emp_id int(11) NOT NULL AUTO_INCREMENT,
uid int(11) NOT NULL,
emp_cat char(50) NOT NULL,
TAN char(25) NOT NULL,
emp_name varchar(255) NOT NULL,
emp_address varchar(255) NOT NULL,
emp_city varchar(100) NOT NULL,
emp_state varchar(100) NOT NULL,
emp_PIN int(11) NOT NULL,
PRIMARY KEY (emp_id),
KEY uid (uid)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

These are my three tables, from which I’m trying to extract information.
I’ve forms in my site, where users must fill information which will populate the first two table, while the third employer table is not a must fill information and it will be needed at certain point of time. not always.

So I want to fetch the result, if employer table contain data, then well and good, or else exclude that and fetch the result from the remaining two tables.

While as Jake has suggest it can be done by using two separate individual select method.

So that I’m already using the php-side conditional code. While my eagerness is to know if there is any option available from the sql-side or not.

Paul_Wilkins · April 11, 2010, 2:47am

You’ll want to investigate replacing the INNER JOIN with a [url=“http://en.wikipedia.org/wiki/Join_(SQL)#Left_outer_join”]LEFT OUTER JOIN which allows the left table to retain rows if the right table has no matches.

As for all SQL queries, our MySQL forum is where you will find people who are more expert on database queries.

cools_sonu · April 11, 2010, 2:55am

Hi, all, now I’m again facing the same problem.

now I’ve expanded my code a little bit and it is again not able to fetch the result. Why this is happening can any body tell me ?

Here is my code.



require_once "includes/database.php";

if(isset($_POST['username']))
		{
			$user = $_POST['username'];
			$usql = "select id from users where username='$user'";
			$ures = query($usql);
			$urow = mysql_fetch_assoc($ures);
			$uid = $urow['id'];
		}
		elseif(isset($_POST['email']))
		{
			$email = $_POST['email'];
			$usql = "select id from users where email='$email'";
			$ures = query($usql);
			$urow = mysql_fetch_assoc($ures);
			$uid = $urow['id'];
		}
		else
		{
			$uid = $_SESSION['USER_ID'];
			$user = $_SESSION['USER_NAME'];
		}
		
		$sql = "select * from users u INNER JOIN userinfo ui ON u.id = ui.uid  WHERE u.id=$uid";
		$res = query($sql);
		$row = mysql_fetch_assoc($res);
		
		

echo "<pre>";
print_r($row);
echo "</pre>";

Here is html form

[HIGHLIGHT=“XHTML 1.0 Transitional//EN”]

<form action=“genPdf.php” method=“post” class=“genPdf”>
<fieldset class=“genPdf”>
<legend class=“genPdf”>User Detail</legend>

    &lt;label class="genPdf" for="username"&gt;Username: &lt;/label&gt;
    &lt;input type="text" name="username" class="genPdf" title="Enter user's username here" /&gt;

&lt;span class="genPdf"&gt;OR&lt;/span&gt;

    &lt;label for="email" class="genPdf"&gt;Email: &lt;/label&gt;&lt;br /&gt;
    &lt;input type="text" name="email" class="genPdf" title="Enter user's email here" /&gt;
    
	&lt;input type="submit" name="submit" value="Enter" class="genPdf" /&gt;

&lt;/fieldset&gt;

</form>




[B]And here is my error message[/B]

Query Failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
Last Query was: select * from users u INNER JOIN userinfo ui ON u.id = ui.uid WHERE u.id=


Note: This code is working for $_POST['username'] & $_SESSION.  but not working for $_POST['email'].

I've tried to make a switch case statement too, also tried by braking down the if else statement but it is not working in either way.

My switch case statement was


```php


require_once "includes/database.php";

if(isset($_POST))
		{
switch($_POST)
{
case "username":
			$user = $_POST['username'];
			$usql = "select id from users where username='$user'";
			$ures = query($usql);
			$urow = mysql_fetch_assoc($ures);
			$uid = $urow['id'];
break;

case "email":
			$email = $_POST['email'];
			$usql = "select id from users where email='$email'";
			$ures = query($usql);
			$urow = mysql_fetch_assoc($ures);
			$uid = $urow['id'];
break;
		}
}
		else
		{
			$uid = $_SESSION['USER_ID'];
			$user = $_SESSION['USER_NAME'];
		}
		
		$sql = "select * from users u INNER JOIN userinfo ui ON u.id = ui.uid  WHERE u.id=$uid";
		$res = query($sql);
		$row = mysql_fetch_assoc($res);
		
		

echo "&lt;pre&gt;";
print_r($row);
echo "&lt;/pre&gt;";

How can I make it work ?

Paul_Wilkins · April 11, 2010, 3:01am

Cause
That error is occurring due to the u.id= not having a value in the SQL statement.

Reason
The code is using $uid for the value, but it appears that $uid is empty.

$uid is obtained from a session variable called $_SESSION[‘USER_ID’]

Action
You need to investigate why there is no user id in the session variable.

cools_sonu · April 11, 2010, 3:06am

Thanx Paul. You made my day. I was working on this from yesterday. And today also after waking up I just tried to solve this issue worked nearly 1 hr and then came here and you are the savior of my day. Now as morning here and I’ve not left the bed yet.

Thanx really v.much.

As for all SQL queries, our MySQL forum is where you will find people who are more expert on database queries.

Actually I’m always confused where to post php-sql related questions. So I always tend to post on PHP forum.

Can it be possible to take this thread to mysql or sql forum ?

Congratulation to myself. Now I’m on the rank of zealot. double bonanza today

Topic		Replies	Views
Can´t retrieve data from database PHP	3	815	July 14, 2010
PHP database fetch issue PHP	14	2363	August 19, 2018
mysql_fetch_assoc = Big problem for me PHP	10	782	February 27, 2010
Trouble pulling data from MySQL PHP	3	599	October 8, 2014
Grabbing data from DB PHP	8	1243	October 8, 2014

Php mysql fetch problem

Related topics