The Cache: Technology Expert's Forum
 
*
Welcome, Guest. Please login or register. October 14, 2019, 06:25:31 PM

Login with username, password and session length


Pages: [1] 2
  Print  
Author Topic: mysql_fetch_?  (Read 14624 times)
cdc
Expert
****
Offline Offline

Posts: 105


View Profile
« on: April 27, 2007, 09:11:48 AM »


What's the best mysql_fetch_ function to use for performance?

I'm talking about:

mysql_fetch_object
mysql_fetch_row
mysql_fetch_array
mysql_fetch_assoc

Does it matter?
Logged

Will code for food.
grandpa
Rookie
**
Offline Offline

Posts: 20



View Profile
« Reply #1 on: April 27, 2007, 09:58:55 AM »

1st post!!

i dont know, i always use mysql_fetch_array.
never tried mysql_fetch_assoc & mysql_fetch_object
Logged
perkiset
Olde World Hacker
Administrator
Lifer
*****
Offline Offline

Posts: 10096



View Profile
« Reply #2 on: April 27, 2007, 11:22:41 AM »

fetch_row and fetch_array are clearly the most often used.

fetch_row is the lowest impact because it maps the fields you SELECTed to numeric indicies only - so if you do somthing like select count('x') from contacts you are certain that row[0] will be the one you want. fetch_array also returns the numeric indicies (little known item) but also creates array entries that are name-indexed to the field you selected. so select fname, lname from contacts would give you array entries of 0, 1, fname and lname.

I do not use the other two because a) I don't do objects in mysql and b) I have never tried the assoc, although I see that it is used all over the SMF code and that triggered me to give it a whirl. Given that it is in this codebase, it certainly must be stable and reliable.

/p
Logged

It is now believed, that after having lived in one compound with 3 wives and never leaving the house for 5 years, Bin Laden called the U.S. Navy Seals himself.
cdc
Expert
****
Offline Offline

Posts: 105


View Profile
« Reply #3 on: April 27, 2007, 12:01:18 PM »

Well, it sounds like we have all the bases covered because I use fetch_object almost exclusively. I'm not sure why, but it probably has something to do with me learning PHP after using Java for 5 years and latching on to anything OO.

Quote
I don't do objects in mysql

Just to clarify, using fetch_object doesn't imply that you do anything different in your database. After the fetch you get an object back and you can access your data like so:

Code:
$obj = mysql_fetch_object($result);
$id = $obj->id;
$name = $obj->name;

I was wondering if there was a de facto way of doing this and why...
Logged

Will code for food.
vsloathe
vim ftw!
Global Moderator
Lifer
*****
Offline Offline

Posts: 1669



View Profile
« Reply #4 on: April 27, 2007, 01:09:14 PM »

I love it when you use Latin vernacular CDC.
Logged

hai
Caligula
Rookie
**
Offline Offline

Posts: 39



View Profile
« Reply #5 on: April 27, 2007, 06:33:11 PM »

This is interesting...  the only one I can ever get to work is mysql_fetch_array... I have tried all the others and nothing seems to do the job I need other than fetch_array plus if you use MYSQL_BOTH as the result type it should return an associative array as well as  a numeric array... so you can kind of catch both and use what you need...
Logged
thedarkness
Lifer
*****
Offline Offline

Posts: 585



View Profile
« Reply #6 on: April 27, 2007, 09:50:49 PM »

This is interesting...  the only one I can ever get to work is mysql_fetch_array...

Do a print_r or var_dump on the result dude, that way you can see exactly what you are dealing with.

Cheers,
td
Logged

"I want to be the guy my dog thinks I am."
 - Unknown
Caligula
Rookie
**
Offline Offline

Posts: 39



View Profile
« Reply #7 on: April 29, 2007, 05:11:04 PM »

Oh so thats what the damn 'var_dump' is used for.... php.net describes it as var_dump Dumps information about a variable  ..... I just automatically assumed it did the same job as 'unset()'  Thanks Bro
Logged
Karlo
n00b
*
Offline Offline

Posts: 6

N00b


View Profile
« Reply #8 on: May 14, 2007, 03:41:37 PM »

I am usually usign fetch_assoc because the fetch_object doesn't work for non-ascii values.
No, not that I'd name a column "Karlo's links".
It's a just-in-case thing...

K.
Logged

No links in signatures please
vsloathe
vim ftw!
Global Moderator
Lifer
*****
Offline Offline

Posts: 1669



View Profile
« Reply #9 on: August 09, 2007, 06:52:16 AM »

Late to the party as usual, but I usually use fetch_array with ASSOC because it's easy for me to remember the column names (in most instances), being first and foremost (and by all formal training) a database developer.

On a side note, I just got the php_mssql lib to actually work which is really nice, because I didn't want to have to port all my data to MySQL and I REALLY didn't want to have to brush up on my ASP.NET when I'm pretty good at PHP. Though I'll always respect MySQL and use it frequently, I really like abilities I have now because of the tools I have in MS's management studio (which was of course already paid for, so why not use it?) and a language that I'm fairly familiar with. Using Transact-SQL, stored procedures, and PHP to do some pretty funky stuff now.
Logged

hai
perkiset
Olde World Hacker
Administrator
Lifer
*****
Offline Offline

Posts: 10096



View Profile
« Reply #10 on: August 09, 2007, 07:53:41 AM »

Glad to know you're there... between you and NBs I know who to go to if I have trouble with a new job I just got - all PHP and MSSQL

/p
Logged

It is now believed, that after having lived in one compound with 3 wives and never leaving the house for 5 years, Bin Laden called the U.S. Navy Seals himself.
vsloathe
vim ftw!
Global Moderator
Lifer
*****
Offline Offline

Posts: 1669



View Profile
« Reply #11 on: August 09, 2007, 08:24:52 AM »

Perks I need some help with something, it should be pretty straightforward. I will have to talk to you later via IM about it. I basically made a little web page where the user can select fields from a dropdown and then conditions (= or CONTAINS), and that affects the SQL query. It's not really that bulletproof, very susceptible to injection which I'm not worried about at this point because the users who will be using it don't know anything about databases and they are all local in this office (won't be a widely deployed app). Anyway, I want to have an "OR" and "AND" button which will add another row of fields and conditions, but I don't know how I should do it. I figure I can do it pretty easily in javascript, but then all the switch statements in the PHP are going to start getting a little hairy. I'm sure you've done stuff way more advanced so I'm looking for guidance.
Logged

hai
perkiset
Olde World Hacker
Administrator
Lifer
*****
Offline Offline

Posts: 10096



View Profile
« Reply #12 on: August 09, 2007, 09:07:40 AM »

I C U...
Logged

It is now believed, that after having lived in one compound with 3 wives and never leaving the house for 5 years, Bin Laden called the U.S. Navy Seals himself.
vsloathe
vim ftw!
Global Moderator
Lifer
*****
Offline Offline

Posts: 1669



View Profile
« Reply #13 on: August 10, 2007, 04:49:01 AM »

As a followup, I basically made an incrementer with the javascript so that the user could have a limitless number of ANDs and ORs added on, then in my PHP I do a for loop where I did $i<(count($_POST)/4) since there are about 4 variables to each of the conditions to formulate my SQL query.

Then I did something cool that someone somewhere might be interested in, I gave the user a button "download to excel spreadsheet". It looks like this:

Code:
<?php
echo'
<form action="download.php" method="POST">
<input type="hidden" name="query" value="'
.urlencode($query).'">
<input type="submit" value="Download as Spreadsheet">
</form>
'
;

Then download.php looks like this:

Code:
<?php 
require_once("datagrid.php");
function 
db_connect()
{
$myServer "SERVER"
$myUser "USER"
$myPass "PASS"
$myDB "DB";

$s = @mssql_connect($myServer$myUser$myPass
or die(
"Couldn't connect to SQL Server on $myServer"); 

$d = @mssql_select_db($myDB$s
or die(
"Couldn't open database $myDB"); 
}
db_connect();
$query=urldecode($_POST['query']);
$result mssql_query($query);

header("Content-type: application/vnd.ms-excel"); 
header("Content-Disposition: attachment; filename=Extraction.xls"); 
header("Pragma: no-cache"); 
header("Expires: 0"); 
DataGrid($result);  
?>


It works pretty sexily. The only hangup is that it's not truly in Excel format so Excel bitches the first time you open it up, but it's just being a pain because it knows how to read it and you can re-save it properly formatted.
Logged

hai
vsloathe
vim ftw!
Global Moderator
Lifer
*****
Offline Offline

Posts: 1669



View Profile
« Reply #14 on: August 10, 2007, 05:27:05 AM »

Here is the solution that I came up with, with all the sensitive information changed.

If anyone has suggestions as far as hacking on some security, I'd appreciate it.

Code:
<?php
/****************************************************
Query.php - This page formulates SQL queries from user input.
It gives the user a very fine grain of control over the queries and
allows him or her to see all the columns in a particular table in your
database, so I would only use it in a trusted environment. The design
could definitely stand some improvement, but I don't claim to be a
designer.
-Drew
8/10/2007
****************************************************/
require_once("functions.php");//All functions contained in functions.php.
?>

<HTML>
<head>
<script type="text/javascript">
var i=0;
function addAND()
{
i=i+1;
  var newrow;
  newrow='<TABLE border="0"><TR><TH colspan="3">AND<TR><TH>Column:<TH>Condition:<TH>Query:<TR><TD><select name="field'+i+'"><?printfields();?></select><TD><select name="condition'+i+'"><option value ="=">=</option><option value ="CONTAINS">CONTAINS</option><option value ="!=">NOT =</option><option value ="NOT CONTAIN">DOES NOT CONTAIN</option></select><TD><input type="text" name="q'+i+'"><input type="hidden" name="andor'+i+'" value="AND"></TABLE>';
  document.getElementById('issues').innerHTML+=newrow;
}
function addOR()
{
i=i+1;
  var newrow;
  newrow='<TABLE border="0"><TR><TH colspan="3">OR<TR><TH>Column:<TH>Condition:<TH>Query:<TR><TD><select name="field'+i+'"><?printfields();?></select><TD><select name="condition'+i+'"><option value ="=">=</option><option value ="CONTAINS">CONTAINS</option><option value ="!=">NOT =</option><option value ="NOT CONTAIN">DOES NOT CONTAIN</option></select><TD><input type="text" name="q'+i+'"><input type="hidden" name="andor'+i+'" value="OR"></TABLE>';
  document.getElementById('issues').innerHTML+=newrow;
}
</script>
</head>
<body>
<div id="formWrapper">
<div id="formDiv">
<form action="<?$_SERVER['php self'];?>" method="POST">
<div><input id="submitButton" type="submit" value="Submit Query"/>&nbsp;<input type="button" value="Add AND Condition" onclick="addAND();"/>&nbsp;<input type="button" value="Add OR Condition" onclick="addOR();"/></div>
<div id="issues"><TABLE border="0"><TR><TH>Column:<TH>Condition:<TH>Query:<TH>Sort By:<TR><TD><select name="field0"><?printfields();?></select><TD><select name="condition0"><option value ="=">=</option><option value ="CONTAINS">CONTAINS</option><option value ="!=">NOT =</option><option value ="NOT CONTAIN">DOES NOT CONTAIN</option></select><TD><input type="text" name="q0"><TD><select name="order0"><?printfields()?></select></TABLE></div>
</form>
</div>
</div>
</body>
</html>
</HTML>

<?php
$query
="SELECT * ";//Begin constructing query.
$query.="FROM TABLE ";

if(isset(
$_POST['q0']))//If first POST variable is set
{
for($i=0;$i<(count($_POST)/4);$i++)//Determine how many additional ANDs or ORs we have by looping through.
{
if(isset($_POST['q'.$i]))//If Nth POST variable is set.
{
$q=$_POST['q'.$i];//assign values.
$field=$_POST['field'.$i];
$condition=$_POST['condition'.$i];
$mod="WHERE";//Default value of modifier is WHERE. Changes to AND or OR for either of those.

switch($condition)//Determine operand condition.
{
case "=":
$cond="=";
$newq="'".$q."'";
$q=$newq;
break;
case "CONTAINS";
$cond="LIKE";
$newq="'%".$q."%'";
$q=$newq;
break;
case "!=";
$cond="=";
$newfield="NOT ".$field;
$field=$newfield;
$newq="'".$q."'";
$q=$newq;
break;
case "NOT CONTAIN";
$newfield="NOT ".$field;
$field=$newfield;
$cond="LIKE";
$newq="'%".$q."%'";
$q=$newq;
break;

}
if(isset($_POST['andor'.$i]))//If it's a secondary condition ORed or ANDed on.
{
$andor=$_POST['andor'.$i];
switch($andor)//Determine operand.
{
case "AND":
$mod="AND";
break;
case "OR":
$mod="OR";
break;
}
}

$query.="$mod $field ";//Construct latter part of query.
$query.="$cond $q ";
}

}
$order=$_POST['order0'];//Determine what to order on.
$query.="ORDER BY $order";

db_connect();//Connect to database.

$result mssql_query($query);//Execute Query
$numrows mssql_num_rows($result);

echo("<b>$numrows rows returned.</b><br /><br />");//Number of rows returned.
echo'
<form action="download.php" method="POST">
<input type="hidden" name="query" value="'
.urlencode($query).'">
<input type="submit" value="Download as Spreadsheet">
</form>
'
;//Button to download as Excel Spreadsheet.
DataGrid($result);//Display results in data grid.
}
?>


Here is the functions.php:

Code:
<?php
function db_connect()
{
$myServer "SERVER"
$myUser "USER"
$myPass "PASS"
$myDB "DB";

$s = @mssql_connect($myServer$myUser$myPass
or die(
"Couldn't connect to SQL Server on $myServer"); 

$d = @mssql_select_db($myDB$s
or die(
"Couldn't open database $myDB"); 
}

function 
printfields()
{
db_connect();
$qq=mssql_query("SELECT * FROM TABLE");
$num=mssql_num_fields($qq);

for(
$i=0;$i<=$num;$i++)
{

echo('<option value ="['.@$field=mssql_field_name($qq,$i).']">'.@$field=mssql_field_name($qq,$i).'</option>');
}
}

function 
DataGrid($result)
{
if(mssql_num_rows($result)==0)//If the query did not return any rows...
{
echo"No data to display.";
}
else
{
echo('<TABLE border="2">');//Make a table.
$num=mssql_num_fields($result);
echo('<TR>');
for($i=0;$i<=$num;$i++)//Print each field name as a column header.
{
echo('<TH>'.@$field=mssql_field_name($result,$i));
//The @ supresses the error when we run out of field names.
}
while($row mssql_fetch_array($result,MSSQL_ASSOC)) 

echo('<TR>');//Begin the table row.
foreach($row as $a)
{
if($a != '' && $a != 'REF!' && $a != '0' && $a != ' ')
//My data has a bunch of junk in it, you can remove this part if yours is clean.
{
echo('<TD>'.$a);//Echo each piece of data as a <TD> element.
}
else
{
echo('<TD>-');//Easier on the eyes than having all that gibberish in the table.
}
}


}

}
?>

« Last Edit: August 10, 2007, 05:29:48 AM by vsloathe » Logged

hai
Pages: [1] 2
  Print  
 
Jump to:  

Perkiset's Place Home   Best of The Cache   phpMyIDE: MySQL Stored Procedures, Functions & Triggers
Politics @ Perkiset's   Pinkhat's Perspective   
cache
mart
coder
programmers
ajax
php
javascript
Powered by MySQL Powered by PHP Powered by SMF 1.1.2 | SMF © 2006-2007, Simple Machines LLC
Seo4Smf v0.2 © Webmaster's Talks


Valid XHTML 1.0! Valid CSS!