Page 1 of 1

MySQL Queries

Posted: Fri Apr 19, 2002 9:45 am
by timmy
I've gotten used to using mysql_fetch_array() to grab info from a db and display all the info in table rows. If I want to just grab the info from one row now, should I instead be using mysql_fetch_row(), or is there much difference efficiency wise? i.e:

Code: Select all

while ($x = mysql_fetch_array($query)
{
$blah = $xї"blah"]
}
їcode]

or should is use:
їcode]
while ($x = mysql_fetch_row($query)
{
$blah = $xї"blah"]
}
thanks

Posted: Fri Apr 19, 2002 9:58 am
by jason
Well, mysql_fetch_(row|array|assoc) basically all do the same thing. I would suggest either using the *row or *assoc functions for your needs, but either work well enough.

Posted: Fri Apr 19, 2002 10:55 am
by sam
Yeah fetch_assoc returns an array with only the keys=>values and fetch_row returns the subscript=>values while fetch array returns both keys=>values and subscript=>values. I would take jason's advice and use on of the former because if you have large returns they will save you roughly 1/2 of the memory.

Cheers Sam

Posted: Fri Apr 19, 2002 12:07 pm
by jason
ahh..but then there is the tradeoff.

What happens when you change the SQL query and add more rows to fetch, or take a few out?

You have to start working out which field is what.

For me, assoc() has never been a problem memory or speed wise. There are many other places you can improve performance.

Besides, assoc() just makes things easier. :D

Posted: Fri Apr 19, 2002 4:27 pm
by sam
You misunderstood me, I was saying that fetch_array is more memory intensive than fetch_row or fetch_assoc. I personally use fetch_assoc also.

Cheers Sam

Posted: Fri Apr 19, 2002 5:26 pm
by timmy
Thanks for the input. Another question:

I'm used to using mysql_fetch_array|assoc() and then using a while loop to itterate through the data and print a table with all of the rows and columns in the database. What would I use if I only want to fetch one row, and assign variables to the data in the db? Do I still need to use a while/for loop to assign each vakue extracted to a variable? What I need to do is fetch profile information for a members section of a webpage, then assign each value as a variable, and register those variables in a session. In order to get away from the "$variable = $row["array_value"]" way of doing things, could I use some other function that may require less cycles to assign each value as a variable?

hope that's not too confusing.

thanks

Posted: Fri Apr 19, 2002 8:48 pm
by sam
you mean that you are only going to get results from one row? if so just do this.

Code: Select all

$row = mysql_fetch_assoc(mysql_query($sql));
If you want to load the resulting array into varables. do this:

Code: Select all

list($a,$b,$c) = mysql_fetch_assoc($result);
I don't really understand the question so that is all I can give you for advice.

Cheers Sam

Posted: Sat Apr 20, 2002 9:00 am
by timmy
OK, here's what I've got:

Code: Select all

/* connect to db and execute memberLoginQuery */
$connection = mysql_connect($db_host, $db_user, $db_pass) or die ("Unable to connect!
" .mysql_error());
$memberLoginQuery = "SELECT * FROM testLogin WHERE memberUserName = '$memberUserName' AND memberPasswd = password('$memberPasswd')";
$memberLoginResult = mysql_db_query("adv", $memberLoginQuery, $connection) or die ("Error in query:
 $query.
 " . mysql_error());
/* if row exists - login/pass is correct */
	if (mysql_num_rows($memberLoginResult) == 1)
	{
		/* initiate a session */
		session_start();
		/* make variables from sql data */
		while ($memberLoginRow = mysql_fetch_assoc($memberLoginResult))
		{
		$memberID = $memberLoginRowї"memberID"];
		$memberUserName = $memberLoginRowї"memberUserName"];
		$memberPasswd = $memberLoginRowї"memberPasswd"];
		$memberEmail = $memberLoginRowї"memberEmail"];
		}
		/* query db for profile information */
		$memberProfileQuery = "SELECT * FROM testProfile WHERE memberUserName = '$memberUserName'";
		$memberProfileResult = mysql_query($memberProfileQuery);
		while ($memberProfileRow = mysql_fetch_assoc($memberProfileResult))
		{
		$memberFirstName = $memberProfileRowї"memberFirstName"];
		$memberLastName = $memberProfileRowї"memberLastName"];
		$memberCity = $memberProfileRowї"memberCity"];
		$memberCategory = $memberProfileRowї"memberCategory"];
		$memberAge = $memberProfileRowї"memberAge"];
		$memberGender = $memberProfileRowї"memberGender"];
		$memberImage = $memberProfileRowї"memberImage"];
		$memberStatus = $memberProfileRowї"memberStatus"];
		$memberReferredBy = $memberProfileRowї"memberReferredBy"];
		$memberDescr = $memberProfileRowї"memberDescr"];
		$memberDescrWord1 = $memberProfileRowї"memberDescrWord1"];
		$memberDescrWord2 = $memberProfileRowї"memberDescrWord2"];
		$memberDescrWord3 = $memberProfileRowї"memberDescrWord3"];
		}
		/* qury db again to get values based on id numbers in the profile table */
		$memberProfileQuery2 = "SELECT statusDescr,categoryName,genderValue FROM memberStatus,memberCategory,memberGender WHERE statusID='$memberStatus' AND categoryID='$memberCategory' AND genderID='$memberGender';";
		$memberProfileResult2 = mysql_query($memberProfileQuery2);
		while ($memberProfileRow2 = mysql_fetch_assoc($memberProfileResult2))
		{
		$memberStatus = $memberProfileRow2ї"statusDescr"];
		$memberCategory = $memberProfileRow2ї"categoryName"];
		$memberGender = $memberProfileRow2ї"genderValue"];
		}
		/* get descriptive words */
		$memberDescrWord1Query = "SELECT descrWord FROM memberDescrWords,testProfile WHERE memberDescrWords.wordID=testProfile.memberDescrWord1 AND testProfile.memberUserName LIKE '$memberUserName'";
		$memberDescrWord1Result = mysql_query($memberDescrWord1Query);
		while ($memberDescrRow1 = mysql_fetch_assoc($memberDescrWord1Result))
		{
		$memberDescrWord1 = $memberDescrRow1ї"descrWord"];
		}
		/* get descriptive words */
		$memberDescrWord2Query = "SELECT descrWord FROM memberDescrWords,testProfile WHERE memberDescrWords.wordID=testProfile.memberDescrWord2 AND testProfile.memberUserName LIKE '$memberUserName'";
		$memberDescrWord2Result = mysql_query($memberDescrWord2Query);
		while ($memberDescrRow2 = mysql_fetch_assoc($memberDescrWord2Result))
		{
		$memberDescrWord2 = $memberDescrRow2ї"descrWord"];
		}
		/* get descriptive words */
		$memberDescrWord3Query = "SELECT descrWord FROM memberDescrWords,testProfile WHERE memberDescrWords.wordID=testProfile.memberDescrWord3 AND testProfile.memberUserName LIKE '$memberUserName'";
		$memberDescrWord3Result = mysql_query($memberDescrWord3Query);
		while ($memberDescrRow3 = mysql_fetch_assoc($memberDescrWord3Result))
		{
		$memberDescrWord3 = $memberDescrRow3ї"descrWord"];
		}
	/* register session variables */
	session_register("memberID","memberUserName","memberFirstName","memberLastName","memberPasswd","memberEmail","memberCategory","memberDescr","memberReferredBy","memberImage","memberAge","memberGender","memberDescrWord1","memberDescrWord2","memberDescrWord3","memberStatus","memberCity");
	/* Redirect to members section */
	header("Location: ../../pages/members/index.phtml");
	/* Free the query */
	mysql_free_result($memberLoginResult);
	mysql_free_result($memberProfileResult);
	mysql_free_result($memberProfileResult2);
	mysql_free_result($memberDescrWord1Result);
	mysql_free_result($memberDescrWord2Result);
	mysql_free_result($memberDescrWord3Result);
	/* close connection */
	mysql_close($connection);
	}
	else
	/* login/pass check failed */
	{
	/* Free the query */
	mysql_free_result ($memberLoginResult);
	/* close connection */	
	mysql_close($connection);
	/* redirect to Login page */
	Header("Location: ../../index.phtml?error=1");
	exit;
	}
}
This is the login script. The member logs in, and the username and password are verified. If the password and username are valid, a whiule loop is used to itterate through the database feilds and create variables from the database info, which are then registered as session variables. I;ve normalized the database into 6 tables, so the fields "memberGender", "memberStatus", "memberCategory","memberCity", "memberDescrWord1", "memberDescrWord2", "memberDescrWord3" are simply filled with numbers, which correlate with the ID munbers in the respective tables in the database. After the first query is done, all the profile information is put into variables. Then, I need to perform more queries in order to turn the nmbers into actual data from the other tables. I may be able to use table joins, yet I'm not that advanced yet with MySQL queries. If this is still confusing you, I can send you an email so you can look at my db, that mat help.

Posted: Tue Apr 23, 2002 1:17 am
by Zmodem
I read you loud and clear.

Congratulations. You are not at the edge of MySQL. This is as far as version 3 goes my friend :)

I have run, and am running, into the exact same problem. Here is my take on the situation, and anyone is welcome to correct me on this.

Yes, you could use JOINs. But I believe you could only convert one uniqueid to text at a time, and would still have to itterate through the fields in the database. Basically, it wouldn't be saving you any code or queries to use JOINs.

One option I have found, is to use something called Sub Selects. Basically, this is a select within a select. Or nested Select if you will. It goes something like this:

Code: Select all

SELECT * FROM users WHERE id = (SELECT * FROM <tablename> WHERE <something> = <something_else>");
Don't quote me on the syntax please :)

So whats the prob? MySQL version 3 does not support subselects. Version 4 does, but its brand new and not even in beta i think.

MSSQL, Oracle, etc etc, all support sub selects, as does postgres sql.

Ok, now, turn around and go back the way you came, because the MySQL roads ends here buddy ;)

Posted: Tue Apr 23, 2002 9:12 am
by timmy
haha, thanks for the reply. that answers my question i guess. i don't plan on switching to v4 until it's released full and stable. i'll just have to hone my SQL skills until then. I'm sure MySQL will come around eventually.