Query Syntax

Regular Joined: Sep 03, 2010 Posts: 57

Sigh... another issue I can't seem to find the answer to.

I am trying to grab the group id from the _nsngr_users table where the users id matches. I have tried multiple ways of getting this value... however, The closest I've come is getting "Resource #93" or "Array".

code I've tried:

Code:

$mpgid = "SELECT gid FROM" . $prefix . "_nsngr_users WHERE uid=\''" . $userinfo['user_id'] . '\'';


$mps_gid = mysql_query($mpgid);

or

Code:

$mps_gid = $db->sql_query('SELECT gid FROM ' . $prefix . '_nsngr_users WHERE uid =\'' . $userinfo['user_id'] . '\'');

or

Code:

$mps_gid = $db->sql_query('SELECT gid FROM ' . $prefix . '_nsngr_users WHERE uid =\'' . $userinfo['user_id'] . '\'');


         $mpgid = $db->fetchrow($mps_gid);

am I not displaying/returning the results back correctly/at all?

Site Admin/Owner Joined: Aug 27, 2002 Posts: 17088

Try this code. It is meant to show exactly what your query is resolving to. I haven't tested it as I don't know where you are placing it.

Code:




echo '<br /><br />$userinfo[\'user_id\'] ' = $userinfo['user_id'];


echo '<br /><br />';


echo 'SELECT `gid` FROM `' . $prefix . '_nsngr_users` WHERE `uid` =\'' . $userinfo['user_id'] . '\'');


echo '<br /><br />';

Is everything resolving correctly?

Posted: Fri Oct 01, 2010 4:56 am

Code:




$mps_gid = $db->sql_query('SELECT gid FROM ' . $prefix . '_nsngr_users WHERE uid =\'' . $userinfo['user_id'] . '\'');


         $mpgid = $db->mysql_fetchrow($mps_gid);

mysql_fetchrow

Posted: Fri Oct 01, 2010 7:15 am

Well it's going to be a hidden input that the end user never see's... but to check my query syntax and make sure it's actually grabbing the data I'm filling a text input with the value.

Just got to work this morn... I'll test it now.

Palbin or Raven... where is good reading on how to structure queries in php? (keep in mind some things are still over my head at this point) I've been using most of my reading and tutorials from tizag.com... but that site shows just the basics.

First edit:

Raven.... Dreamweaver is showing syntax errors with the code you provided.
Attack of the white page. Wink

------

Second edit:

Palbin... with your code.. the syntax seems fine... but i'm being attacked by a white page as well.

Guess I should be wrapping that query with a function?

Here is how I tried yours.

On my form. (to display the gid for now)

In my custom functions.php

Posted: Fri Oct 01, 2010 7:50 am

PHP and MYSQL Web Development by Luke Welling and Laura Thompson. I think there are more than one editions but I have the second edition. Probably can find it in most bookstores.

If you put your query string into a variable, such as:

$sql = 'SELECT gid FROM ' . $prefix . '_nsngr_users WHERE uid =\'' . $userinfo['user_id'] . '\''

You can then echo the sql out (echo $sql . <br /> Wink

to see exactly what you will be executing with the statement
$db->sql_query($sql);

Also, note that if you are executing this under Ravennuke and have your dblog turned on you can see any errors in the dblog.

Here is a complete sequence of code from a query I happen to have open in an editor at the moment:

Code:

    $sql = 'SELECT * FROM '.$prefix.'_household WHERE `Expiration` > \'2010-09-27\'';


    echo $sql . '<br />';


    $result = $db->sql_query($sql);


     $numrows = $db->sql_numrows($result);


     echo 'Households Current ' . $numrows . '<br />';


    while ($row = $db->sql_fetchrow($result)) {


        $hhno = $row['Household_Number'];

hope that helps.

Posted: Fri Oct 01, 2010 8:24 am

Thanks fkelly for the book reference... I'll be looking for it.

------

So then I should write the query like this...

(changing the var name just for consistency)

Code:

$mpgid = 'SELECT gid FROM ' . $prefix . '_nsngr_users WHERE uid =\'' .$userinfo['user_id'] . '\'';

This would store the query inside " $mpgid" which would be displayed by...

[code]echo $sql . '<br />';[code]

on said particular page on w/e table row etc.

Or would the "$result" be the var that holds the actual result of the query?

Sorry to question so much... but I want to learn what I'm doing vs. just having someone do it for me then acting as if I know what I'm doing. Thanks.

Posted: Fri Oct 01, 2010 8:26 am

sorry for double post... no edit.

to correct the second code...

Code:

echo $mpgid . '<br />';

Posted: Fri Oct 01, 2010 8:32 am

mefoo sorry I had a mistake in my previous post.

Palbin wrote:

Code:




$mps_gid = $db->sql_query('SELECT gid FROM ' . $prefix . '_nsngr_users WHERE uid =\'' . $userinfo['user_id'] . '\'');


         $mpgid = $db->mysql_fetchrow($mps_gid);

mysql_fetchrow

It should be sql_fetchrow and not mysql_fetchrow.

Also you really need to turn on error reporting so you see the errors and warnings instead of a white page.

Posted: Fri Oct 01, 2010 8:56 am

Thanks guys for all your help... I really owe y'all big...but it still seems to be evading me.

Palbin-

I used this

Code:

$mps_gid = $db->sql_query('SELECT gid FROM ' . $prefix . '_nsngr_users WHERE uid =\'' . $userinfo['user_id'] . '\'');


        $mpgid = $db->sql_fetchrow($mps_gid);

Along with

Code:

echo '<td><input type="text" name="mps_gid" id="mps_gid" value="' . $mpgid . '"size="13" /></td></tr>'."\n";

To show my result... however, it displays "Array".

-------

fkelly-

I used this

Code:

$mpgid = 'SELECT gid FROM ' . $prefix . '_nsngr_users WHERE uid =\'' .$userinfo['user_id'] . '\'';


echo $mpgid . '<br />';

or

Code:

$mpgid = 'SELECT gid FROM ' . $prefix . '_nsngr_users WHERE uid =\'' .$userinfo['user_id'] . '\'';


$result = $db->sql_query($mpgid);


echo $result . '<br />';

and got "Resource #83" returned back.

Here is the structure of the table. (just for quick reference)

It sure seems like I'm grabbing something from the db... but it's just not returning it or I'm just a complete maroon. I'm betting on the latter.

Posted: Fri Oct 01, 2010 9:34 am

Fkelly-

I used your method to echo out my query... and here is what it is.

code:

Code:

$mpgid = 'SELECT gid FROM ' . $prefix . '_nsngr_users WHERE uid =\'' .$userinfo['user_id'] . '\'';

echo:

Code:

SELECT gid FROM nuke_nsngr_users WHERE uid ='6'

The query seems fine... yet... I'm not returning back the "7" which should be associated with it.

Posted: Fri Oct 01, 2010 10:29 am

Woohoo... got it!!

I used Raven's code and added what I forgot the whole time...

query:

Code:

$mpgid = $db->sql_query('SELECT gid FROM ' . $prefix . '_nsngr_users WHERE uid =\'' . $userinfo['user_id'] . '\'');


        $mps_gid = $db->sql_fetchrow($mpgid);

echo:

Code:

echo $mps_gid['gid'] . '<br />';

Returns "7" Wink

Was forgetting the "['gid']" after $mps_gid.

Thanks guys... I understand it much better now!!! Thanks for all the help and It will NOT go unaccounted for. Wink

(If I can get this project up and running... HOPEFULLY I will get to turn my hobby/passion into a full time paying hobby/passion. I really love RN and the assistance y'all give us up n' comers. Couldn't ask for a better group of people.... y'all are outstanding!!!!)

Posted: Fri Oct 01, 2010 10:52 am

Think of it this way ... MYSQL returns the results in an array. The array name is $mps_gid. The ['gid'] is an element in the array. You need to reference both the array and the element in it to get back the value 7.

Posted: Fri Oct 01, 2010 10:58 am

fkelly wrote:

Think of it this way ... MYSQL returns the results in an array. The array name is $mps_gid. The ['gid'] is an element in the array. You need to reference both the array and the element in it to get back the value 7.

Perfect explanation!! That is what I couldn't understand about the whole entire thing. I really do appreciate y'all and want to make sure you know it. I hope to become solid enough to be apart of the RN team someday.

Side note... you getting cold rain up there today?? I saw the radar last night and it looked like y'all were getting hammered.