mysql fetch array() issue

[Orangutang]

Hello,

I'm trying to display 3 columns from my db using the where clause in the select statement. As much as the syntax errors are useful I'm trying to understand why the method I'm using isn't working. Any pointers, many thanks.

I thought I was doing this:

1. Checking and stipulating the session user.
2. Selecting from the db.
3. Creating a var from the select for debugging.
4. Dumping the vars as effort to find problem.
5. While statement to retrieve row via a var.
6. Echoing out db row data.

But somethings not right, surprise! The error mysql_fetch_array() expects parameter 1 to be resource, string given in....which is the while statements var which points to the select statement. I remove the quotes from the select statement and surrond in () to what I thought made it a resource but the error just says unexpected string on the select statement.

Dumping the vars displays: string(49) "SELECT * FROM quotestest WHERE username = 'steve'"

And the code:

if (! isset($_SESSION['user']) or (isset($_SESSION['user']) and ! ($_SESSION['user'] > 0)))
{
$result = "SELECT * FROM quotestest WHERE username = '". $_SESSION['user'] ."'" ;
mysql_query ($result)or die (mysql_error());

var_dump($result);

while ($row = mysql_fetch_array($result))
{
echo $result;
echo $row['quoteid'] . " " . $row['fromcompany'] . " " . $row['fromcontact'];
}
mysql_close();
}
die("Invalid user");


Any method advice as to a better way would also be really useful, I read all the posts and have gained a lot from all your tips.

Many thanks and any help........

[rocknbil]

Change this

$result = "SELECT * FROM quotestest WHERE username = '". $_SESSION['user'] ."'" ;
mysql_query ($result)or die (mysql_error());
while ($row = mysql_fetch_array($result))

to this.

$query = "SELECT * FROM quotestest WHERE username = '". $_SESSION['user'] ."'" ;
$result = mysql_query ($query) or die (mysql_error());
while ($row = mysql_fetch_array($result))

and it will fly. You execute a query and store it in a result object from which you extract rows - which is why it's helpful (but not required) to use variable names that indicate what they do. :-)

[Orangutang]

Hi rocknbil,

I see exactly what your saying about why its helpful to use variable names, the result is an object with rows in it. Cheers. :-)

Unfortunately still displays Invalid user, am I missing retrieving data before my If statement to check the session user, how could it be going wrong ?

[Matthew1980]

Hi there Orangutang,

Sorry to butt in, but I thought I would ask a question or two :)

Is there a valid connection handle going at this point in the script (I assume there is) because you haven't specified one :) But if you have used one previously in a query somewhere else further upstream, this query will inherit the connection handle from that one ;)

In answer to your question try this, if the data is there as you expect, then there is nothing wrong with the query:-

echo $query = "SELECT * FROM `quotestest` WHERE `username` = '".$_SESSION['user']."' ";
exit;//kill script here to view the populated string
$result = mysql_query ($query) or die (mysql_error());

And provided $_SESSION['user']; holds a value you should see something like:-

SELECT * FROM `quotestest` WHERE `username` = 'JohnDoe'

and nothing else printed to screen, obviously, if $_SESSION['user']; exists, but NOT in the Db you will also get that 'invalid user error' - as that is your own error message ;)

Hope that helps..

Cheers,
MRb

[Orangutang]

Hi Matthew,

No thank you for the butt in, please feel free. :-)

I tried your code and it echo's out exactly what you say it should.

SELECT * FROM `quotestest` WHERE `username` = 'steve'
(steve being the name I logged in with which is the username in the clients table in the db.

And no 'invalid user error'

I created this small test script to evaluate and the only script upstream is the follows:

<?php
session_start();
mysql_connect("localhost","root","edited") or die ("Could not connect to mysql");
mysql_select_db("mybusiness") or die ("No database");

if (! isset($_SESSION['user']) or (isset($_SESSION['user']) and ! ($_SESSION['user'] > 0)))
{
Then the SELECT statement

Its probably something very simple so I shall brace myself for the embarrassment :-)

Many thanks if you can help

[Matthew1980]

hi there orangutang,

Ok.

if (!isset($_SESSION['user']) or (isset($_SESSION['user']) and ! ($_SESSION['user'] > 0)))

If the contents of $_SESSION['user'] is a string and you are checking to see if that's set & correct, try this instead:-

//if it's set see if it contains anything and that its only alphabetical chars
if (isset($_SESSION['user']) && !empty($_SESSION['user']) && ctype_alpha($_SESSION['user']))

or this

//if it's set see if it contains anything and that its only alphabetical chars
if (isset($_SESSION['user']) && !empty($_SESSION['user']) && preg_match("/^[A-Za-z]+$/",$_SESSION['user']))

I think that will do as you are needing to check, though regex isn't my strong suite - Rocknbil may need to set me straight there ;-p

I actually re read the first post & you had already mentioned doing a var_dump - I'll put my glasses on from now on. Doh!

Cheers,
MRb

[Orangutang]

Hi Matthew,

No probs, the code I'm seeing and its still not making sense :-)

I tried both options and each returned exactly the same result:

SELECT * FROM `quotestest` WHERE `username` = 'steve'

Due to the checks does this tell us whats wrong ?

[Matthew1980]

Hi there orangutang,

if (isset($_SESSION['user']) && !empty($_SESSION['user']) && ctype_alpha($_SESSION['user'])){

$query = "SELECT * FROM `quotestest` WHERE `username` = '".$_SESSION['user']."' ";
$result = mysql_query($query)or die (mysql_error());

if(mysql_num_rows($result) > 0){

while ($row = mysql_fetch_array($result)){
//try echoing the actual array to see if the keys/column names are as you have referred to them :)
echo "<pre>";
print_r($row);
echo "</pre>";

echo $row['quoteid']." ".$row['fromcompany']." ".$row['fromcontact'];
}
//query returned no rows
else{
echo "No rows returned";
}

}
else{
echo "User not in db";
exit;
}

Other than that, I'm stumped. My bed is now beckoning me.

Give that a try any way, I have put error handlers just in case there are no rows being returned from the query.

Is this only going to output one result? or is there more than one match in the DB...

This is all based on the assumption that there is a valid db connection handle going, I'm sure there is, but as it is not referenced I have to ask..

Cheers,
MRb

[Orangutang]

I know what your saying, the vision is degrading.

Thanks for your help with this, I just cannot see a possible place for an error? I'll go through it tomorrow and hope for the best.

Thanks again

[rocknbil]

LOL . . . well of course it's saying "invalid user," it's always going to, it's outside your "if." Look:

if (! isset($_SESSION['user']) or (isset($_SESSION['user']) and ! ($_SESSION['user'] > 0)))
{
//cut the code inside the if . . .
}
die("Invalid user");

I think you want an else, like

else { die("Invalid user"); }

I didn't look because you didn't spot this out as a problem in the O.P.

Its probably something very simple so I shall brace myself for the embarrassment.

I'd almost agree w. you if I hadn't done this sort of thing myself. :-) But don't feel dumb . . . sometimes you stare at a thing for so long it starts to look like one of those laser prints they used to sell . . . I just . . . can't . . . see it . . . .

[Orangutang]

Hi rocknbil,

Thanks for the input, I worked on the site on Sunday, spent all day restructuring a script using many different ways, all went well until at end of day kept having a parse error and as last chance saloon I posted. 2 members kindly pointed out I had missed a blatantly obvious curly bracket. LOL :-)

May I ask:

EG: All the select queries I have done from the cmd line work fine, I insert the data into the various tables and it always is retrieved as expected. I also retrieve it from the net no probs. The only difference here is that I'm using a session.

With this I am using a session as the originating filter. I check its the user with the If statement then proceed to select * from quotestest to retriev the quotes the logged in user has sent.

clients table:
clientid | username | password
55...... | steve | 123
57...... | sally | 321

quotestest table:
quoteid | username | fromcompany | fromcontact | toemail
8........ | empty | SDF | dsfd | [e-mail]
19...... | empty | ZCX | dsfc | [e-mail]

I've got a horrible feeling that my problem exists as Matthew pointed out:

//try echoing the actual array to see if the keys/column names are as you have referred to them

Usually all the data is there when I do my queries but here the username is empty in the quotetest table because I thought I was using the session data to filter but maybe I am incorrect.

Summary

Can I ask is there an obvious problem with my table structure, IE: keys/column names ?

*Matthew* Many thanks for your help last night and I apologise in advance if what you pointed out is correct.

I tried the last script you kindly put up, unfortunately it errors out on the else statement else{
echo "No rows returned";
}

When I remove the else and just leave echo "No rows returned" it displays User not in db which I suppose it should do.

I'm sure theres something wrong with keys/column names, I've posted both tables above an if you can see my obvious mistake I thank you in advance. :-)

PS: I remember looking at those laser prints, then looking again and again and again LOL :-)

[Orangutang]

Hi rocknbil,

Sorry, I did of course try your advice by adding in:

else { die("Invalid user"); }

And the screen displays ok with no errors but it is blank.

Does this mean that it is the user but this user is returning nothing so the problem must be in the way I'm selecting which takes me back to Matthews point:

//try echoing the actual array to see if the keys/column names are as you have referred to them

Once again many thanks for all the advice.

[Matthew1980]

Hi there Orangutang,

Your answer:-

I tried the last script you kindly put up, unfortunately it errors out on the else statement else{
echo "No rows returned";
}

Obvious question:

Have you run the query through phpmyadmin (if you have it) to see if the static query:-

SELECT * FROM `quotestest` WHERE `username` = 'steve'

actually returns a value because from what I posted last night, this tells me that the info isn't in the DB and no matter what you code in the answer will be the same - check the actuall db, check the names & values, I have a feeling this is DB side now...

PS: Laser prints ? you mean "magic eyes" I go cross eyed when I tried it ;)

Cheers,
MRb

[Orangutang]

Hi Matthew,

magic eyes, thats the one :-), I thought/hope that was what rocknbil was referring to ?

Many years ago we all used to gather round when a new one came out and start looking but not looking :-) trying to make it appear. Then someone who was good at them came in and saw the pic within a couple of mins. Tinge of jealousy followed by 10mins of cross eyedness :-)

And I tried your suggestion of running the query from php My Admin:

SELECT * FROM `quotestest` WHERE `username` = 'steve'

and the result returned zero rows.

MySQL returned an empty result set (i.e. zero rows). ( Query took 0.0004 sec )

Would this mean its in the db structure. But all I've got is my test db to evaluate:

db name is "mybusiness" with 2 tables in it as identified above, clients and quotetest.

Love the magic eyes, real blast from the past :-)

[Matthew1980]

Hi there orangutang,

Well, at least were getting somewhere now - if you put that query directly into pma and it return no results, there is something wrong in the data base, check to see if the name steve is Steve, because if its capitalised first letter and you ask for steve instead of Steve, you won't get a hit, at least I think that it's case sensitive, I would have to R.T.M to confirm. Memory not what it used to be ;)

Try putting the same static query in but this time use Steve.

Yes though, curiosity how did you structure your DB?

[EDIT]:

clients table:
clientid | username | password
55...... |steve | 123
57...... | sally | 321

quotestest table:
quoteid |username | fromcompany | fromcontact | toemail
8........ | empty | SDF | dsfd | [e-mail]
19...... | empty | ZCX | dsfc | [e-mail]

Looks like from that you need to do a table join to get this query going ;)

Cheers,
MRb

[Orangutang]

Hi Matthew,

Thanks for the pointers, I've tried the caps and it does make a difference, if I log in with Steve, no access but steve, granted. In db username is steve.

Due to this I've implemented the following to tidy up input which I hope is the professional way of coding ?

$username = strtolower(trim($_POST['username']));

Thanks for your advice on table join and I thought that was it so I replaced my select statement from:

$query = "SELECT * FROM quotestest WHERE username = '". $_SESSION['user'] ."'" ;

to (clientid and quoteid are both PK and AI)

$query = SELECT clients.clientid, quotestest.quoteid
FROM clients
INNER JOIN quotestest
ON clients.P_Id = quotestest.P_Id
ORDER BY clients.clientid

But just errors unexpected string. I assume this is because I am not merging the session name into it which I don't know how to do even if that is the correct solution. :-P

Summary:

Its funny but I've tried retrieving the clientid from the db then inserting it into the quotestest table.

I then tried mysql_insert_id to get the AI value from the clients table then tried inserting it into the qotestest table.

I then tried getting the session variable value(steve) and inserting it into quotestest table

I then thought I must be able sort by using the value in the session without inputting it into quotestest table statement first.

But I couldn't get anything to work.

Is the answer as discussed at beginning:

All I need to do is merge the session data with the select statement thats using join tables.

I say all but I can't find an example of it anywhere, tried net w3schools etc.

Or am I still mis-understanding whats happening.

Thanks for all your help, surley/hope must be nearly there ?

[Matthew1980]

Hi there orangutang,

Sounds to me like your asking the wrong table the question ;)

>>if I log in with Steve, no access but steve, granted.

What do you mean by log in - this is a query in a script to extract and display information relative to $_SESSION['user'] data

SELECT * FROM quotetest;

from pma and see if your user name is in what's returned as I don't think it will be. Are you referencing the right table?

If those are the only two rows in quotetest (previous post), then the query will always return 0 rows, you need the username 'steve' in that column in quotetest instead of it being empty, then your query will go and return:-

quotestest table:
quoteid |username | fromcompany | fromcontact | toemail
8........ | steve | SDF | dsfd | [e-mail]
19...... | empty | ZCX | dsfc | [e-mail]

this row:-

8........ | steve | SDF | dsfd | [e-mail]

Hopefully you can see what I am getting at there, If I have got the wrong end of the stick - apologies.

Cheers,
MRb

[Orangutang]

Hi Matthew,

Apologies I should of been clearer. I've created the simplest testing site for evaluation which comprises of login.php, checkpass.php, raisequote.php, sendquote.php and allquotes.php :-)

When I say: >>if I log in with Steve, no access but steve, granted. I just mean that on login.php displays username not found if I use a capital S on Steve when its steve with a small caps in clients table.

I then create a session in checkpass.php. $_SESSION['user'] = $_POST['username'];

I then have session_start on the other pages so keep session data then use it to check if its the user before issuing the select statement.

I tried SELECT * FROM quotestest; from pma.

Just to be sure - I click the db then click query, paste in above and it displays #1064 - You have an error in your SQL syntax. The query window is still open with SELECT statement in it, if I click go again it displays Showing rows 0 - 1 (2 total,) and shows the quotestest table.

Can I confirm, should the username be in the quotestest table as per SELECT * FROM quotetest; would expect.

I this where I'm going wrong, this would mean that somehow I've got to put the clients table client id or the clients table username into the quotestest table username empty column. :-)

Is that correct or would you advise reconstruction of my table join statement.

Many thanks

[Orangutang]

Hi Matthew,

Sorry I responded to only half your post which you have asked to be deleted, I'll read/try it properly :-)

[Orangutang]

Hi Matthew,

No that is spot on, you have identified to me that I need to input the clients table client id or the clients table username into the quotestest table username empty column. :-)

Your advice points to inserting the username in the quotestest table as per your post:

8........ | steve | SDF | dsfd | [e-mail]


Could I ask your advice as to where I should do this.

The only INSERT cmd I have on this testing site is on raisequotes.php when I insert the quote form values.

Would you advise adding into that or creating a new INSERT statement just for the job.

Getting there, many thanks :-)

[Orangutang]

Sorry I meant on sendquote.php

mysql_query("INSERT INTO `quotestest` (`fromcompany`,`fromcontact`,`toemail`) " . "VALUES ('" . $fromcompany . "','" . $fromcontact . "','" . $toemail . "')",$mysql);

[Orangutang]

Hi Matthew,

Many thanks, its done. I'm retrieving only the quotes that that user placed. :-)

As you suggested I sorted a variable for the INSERT cmd which entered the username into the quotestest table. Then created it again for allquotes.php and used it in the select statement.

I've got to sort the display of the data, its all on one line etc but hopefully thats not too bad. :-|!

Thanks again for your time and help, its really appreciated. :-)

[rocknbil]

I did of course try your advice by adding in:
else { die("Invalid user"); }
And the screen displays ok with no errors but it is blank.

Which means

if ([conditions]) {
// it's here, so it's a problem with matching on the data
}

[Matthew1980]

Hi all,

I'm now confused! It's working. That's a good thing?!

If that's the case & you understand where the misunderstanding was on data retrieval so you can now compensate and code the rest accordingly. Insert, select & display should be a simple thing :)

I personally always use Pma to test my sql queries before I use them on the website, especially if they contain dynamically constructed content (I copy and past from what's generated by the script) from there it's easier.

Cheers,
MRb

[Orangutang]

Hi rocknbil,

I think I understand, just by putting else {die("Invalid user");} you restricted the script and made it check the if condition for true or false, is it the session user, if true do first curly brackets, if false {die.....

When it ran this it displayed a blank screen with no errors which showed its ok but there's a problem with missing data.

By knowing its missing data you could look upstream for the missed data which in my case was the missing session variable.

Thats my understanding, hopefully its what you were trying to teach me to do yesterday.

Thanks as always

[Orangutang]

Hi Matthew,

Hopefully I do thanks to yours and rocknbil's posts.

I was missing the session variable, I wrote the following and put it at the start of the code below session start and above the connect.

if(!isset($_SESSION['user']))
{
echo "Log in";
}
else
{
$username = $_SESSION['user'];

if ($username)
{
echo "<b>CUSTOMER Username:</b> $username<br />";
}

mysql_connect.................................

Which as I understand (please correct if wrong) checked it was the session user, if true created a session variable $username, echoed it out to be sure.

Then all I did with select statement was:

$query = "SELECT * FROM `quotestest` WHERE `username` = '".$_SESSION['user']."' ";

Because username now refers to variable $username it worked I'm assuming.

The datas not displayed as wanted but at least its retrieving only the data it should:

CUSTOMER Username: steve
58 dfbdf ffdf57 dfgb fgbdffd55 fghbh gbhb56 fghbh gbhb

There are a lot of quotes in the quotestest table but only these quoteid numbers were placed by steve.

I hope I've understood it all correctly. Please point out if I'm mis-understanding.

The more I look at it the more I think there's an easier way to do it so any suggestion as always would be very welcome.

Thanks again for all the help

[Orangutang]

Sorry Matthew,

Half a story.

Before I wrote the code above for the allqotes.php page I wrote the same code for the sendquote.php page then included the variable $username in my INSERT statement which put the username into the quotestest table.

mysql_query("INSERT INTO `quotestest` (`username`, `fromcompany`,`fromcontact`,`toemail`) " . "VALUES ('" . $username . "', '" . $fromcompany . "','" . $fromcontact . "','" . $toemail . "')",$mysql);

I then saw the username steve in the quotestest table in the username column which used to be empty.

As said hopefully this logic makes sense to you, have I achieved the corect result but in the right way.

[Matthew1980]

Hi there orangutang,

Phew! At least you are getting the data you wanted, now you can start to tidy the data up, build a table (div set if using a css layout - but I think that's for another day :)) and lay things out in a more readable format.

Yes that query looks right to me, there are a few things you can do to make this process more efficient & elegant, but for the nut's and bolt's of it that'll do ;-p

As I say in other posts/threads though, it's best to build sql queries into a variable and not inside the mysql_query() function, just so that any debugging can be done more easily - BUT for sql statements that are static, your method is fine:-

//Dynamic sql
$sqlQuery = "SELECT `something` FROM `".$tableVariable."` WHERE `an_id` = '".$RefId."' ";
$sendQuery = mysql_query($sqlQuery, $connLink) or die(mysql_error());

//debug Dynamic sql
echo $sqlQuery = "SELECT `something` FROM `".$tableVariable."` WHERE `an_id` = '".$RefId."' ";
exit;
$sendQuery = mysql_query($sqlQuery, $connLink) or die(mysql_error());

You see what I mean, I just find this an easier method :)

And you can condense your other code into this:-

$username = (isset($_SESSION['user']) ? $_SESSION['user'] : 'Log in');
variable check set if true assign it if false assign it log in :)

the = just means assign the product of the evaluation...

As Rocknbil stated ages ago - the ternary is your friend (Many thanks Rocknbil :) & DC for more complex nested versions ;))

Loads to learn, I still am!

Have fun with the rest of the project!

Cheers,
MRb

[Orangutang]

Hi Matthew,

I was dreading you saying that I had got the right data but in the wrong way. Just a nuts and bolts retrieval is big progress for me. :-)

I understand in principle what you say about it's best to build sql queries into a variable and not inside the mysql_query() function for an easier debug method and want to implement it. But I don't fully understand whats happening, I need to look into your advice and get to grips with it.

Also thanks for the code to condense my rather lengthy waffle to produce a variable. :-)

And as you say for another day I look forward to learning how to tidy up the data for display.

Thanks again for all your help.