Two MySQL Function

[bundyxc]

So I've been working on this function to submit queries on my site. In theory, you set the type of query (select/insert/etc.), and then the query. From there, it takes the data and creates an array.

 

So in theory, instead of having to do:

 

<?php
$select1 = "INSERT INTO  users (true) VALUES (1)";
$select1result= mysql_query($select,$con) or die(mysql_error().": ".$select1);
$select1numrows = mysql_num_rows($select1result);
$select1fetcharray = mysql_fetch_array($select1result);

$select2 = "SELECT * FROM users WHERE true=1";
$select2result= mysql_query($select,$con) or die(mysql_error().": ".$select2);
$select2numrows = mysql_num_rows($select2result);
$select2fetcharray = mysql_fetch_array($select2result);
?>

 

You would just do

 

<?php
pure_query('insert', 'INSERT INTO  users (true) VALUES (1)');
pure_query('select', 'SELECT * FROM users WHERE true=1');
?>

 

Are there any huges holes in my logic? This just sounds 100 times easier to me. I'm a PHP noob, but it sounded like a shortcut. Any better way to do this, or to write my function? Function included below..

 

 

 

<?php
function pure_query($type, $query)
{
global $con; // Establish what connection the function should be using to run the query.

if ( $type == 'SELECT' || $type == 'SHOW' || $type == 'DESCRIBE' || $type == 'EXPLAIN')
{

$go = mysql_query($query,$con) die (mysql_error() . ": " . $query); // Run the query, or echo the error.
$fetch = mysql_fetch_array($go);                                    // Take the output and create array $var[]
$num = mysql_num_rows($go);                                         // Count the number of affected rows.

$array = array
	(
		'query' => $query, // $var['query'] returns the query.
		'num' => $num,     // $var['num'] returns the number of affected rows.
		'fetch' => $fetch  // $var['fetch'] returns the results of the query.
        ); 

}

$return = $array;
return $return;

elseif ( $type == 'INSERT' || $type == 'UPDATE' || $type == 'DELETE' || $type == 'DROP' )
{

$go = mysql_query($query,$con) die (mysql_error() . ": " . $query); // Run the query, or echo the error.
$fetch = mysql_fetch_array($go);                                    // Take the output and create array $var[]
$num = mysql_affected_rows($go);                                    // Count the number of affected rows.

$array = array
	(
		'query' => $query, //$var['query'] returns the query.
		'num' => $num      //$var['num'] returns the affected rows.
        ); 

}

$return = $array;
return $return;

else
{
$return = echo 'Query type \"' . $query . '\" not recognized. '; // If query type is not supported, notify the user.
return $return
}
}
?>

[kickstart]

Hi

 

Personally not sure the idea would save you any effort.

 

First problem is that on the SELECT you are using mysql_fetch_array which will return one row as an array, hence you would lose any subsequent rows.

 

All the best

 

Keith

[bundyxc]

I have it set up so that $var['fetch'] would return the row (and $var would then contain a multidimensional array).

 

So the programmer would be able to do something like this:

 

<?php

while ( $var = php_query('select', 'SELECT * FROM names WHERE 1')
{
   echo $var['fetch']['firstName'] . $var['fetch']['lastName'];
}

?>

 

Does that make sense, or is my logic faulty? By the way, I'm curious as to why you don't think it would save effort, or if there's a better way to do it. I just find it irritating to constantly be calling the variables from the above query, whereas the only change is the query being run. It may just be the nature of the program I'm making (as it's quite repetitive in nature), but I see this as a huge time saver.

 

I just like to hear the opinions of people who are more experienced. Thanks so much for your reply Keith.

[roopurt18]

What kickstart said is correct.  Your select method is only going to return one row.  Also, php_query() never returns false so your loops will be infinite.

 

echo $var['fetch']['firstName'] . $var['fetch']['lastName'];

Congrats.  You've just given yourself 10 extra characters to type every time you want to access a field (by having to type ['fetch']).

 

I have to wonder why you don't just use PDO?

 

Assuming $pdo is already an instantiated PDO object.

$insert = $pdo->prepare( "insert into `yourtable` ( `first_name`, `last_name` ) values ( ?, ? )" );
if( $insert ) {
  $insert->execute( array( 'Larry', 'Smith' ) );
  echo $insert->rowCount() . "\n";
  $insert->execute( array( 'John', 'Jones' ) );
  echo $insert->rowCount() . "\n";
}

$q = $pdo->query( "select * from `yourtable` where 1=1 order by `last_name`" );
if( $q ) {
  echo $q->rowCount() . " people\n";
  while( $row = $q->fetchObject() ) {
    echo $row->last_name . ', ' . $row->first_name . "\n";
  }
}

 

Not that hard really.

[bundyxc]

What kickstart said is correct.  Your select method is only going to return one row.  Also, php_query() never returns false so your loops will be infinite.

 

Oops, I meant to type this code. Now I get what he was saying:

 

<?php

while ( $row = $var['fetch'])
{
   echo $row['firstName'] . $row['lastName'];
}

?>

 

Would that work?

 

I have to wonder why you don't just use PDO?

 

Great question. I don't know PHP OOP, so I haven't learned PDO. I'm trying to learn procedural first, and I'll learn object oriented from there. I haven't the slightest what PDO is, and I can't read your code. Why? Because I'm a noob. I'm not stupid, I just haven't learned it, that's all.

 

I'm sure that I'll be able to move onto bigger and better things once I've learned my fair share of procedural coding.

[roopurt18]

Top-level documentation for PDO.

http://www.php.net/manual/en/book.pdo.php

 

The PDO class:

http://www.php.net/manual/en/class.pdo.php

 

The PDOStatement class:

http://www.php.net/manual/en/class.pdostatement.php

 

Whenever you feel like you're ready to tackle it, PDO presents a much more simple way of interacting with your database.  All you do is create a new PDO object:

$pdo = new PDO( $dsn );

And then run prepare() or query() calls.

 

If you played with the examples enough you might actually be able to use it without fully understanding OOP.

 

Anyways...if you look at your function you wrote:

$go = mysql_query($query,$con) die (mysql_error() . ": " . $query); // Run the query, or echo the error.
$fetch = mysql_fetch_array($go);                                    // Take the output and create array $var[]
$num = mysql_num_rows($go);                                         // Count the number of affected rows.

$array = array
	(
		'query' => $query, // $var['query'] returns the query.
		'num' => $num,     // $var['num'] returns the number of affected rows.
		'fetch' => $fetch  // $var['fetch'] returns the results of the query.
        ); 

 

You are calling mysql_fetch_array() and returning that.  mysql_fetch_*() fetches a row from the result-set, not the result-set itself.  So your function is running the query, fetching the first row returned, and returning that to the user.

 

What you want to return is the result of mysql_query() (which will be the result-set).

 

$go = mysql_query($query,$con) die (mysql_error() . ": " . $query); // Run the query, or echo the error.
        // DELETE THE NEXT LINE
//$fetch = mysql_fetch_array($go);                                    // Take the output and create array $var[]
$num = mysql_num_rows($go);                                         // Count the number of affected rows.

$array = array
	(
		'query' => $query, // $var['query'] returns the query.
		'num' => $num,     // $var['num'] returns the number of affected rows.
                        // NOTICE THE DIFFERENCE ON THE NEXT LINE
		'fetch' => $go  // $var['fetch'] returns the results of the query.
        ); 

[kickstart]

Oops, I meant to type this code. Now I get what he was saying:

 

<?php

while ( $row = $var['fetch'])
{
   echo $row['firstName'] . $row['lastName'];
}

?>

 

Would that work?

 

Not really. You would need to modify your original function. Something like this:-

 

if ( $type == 'SELECT' || $type == 'SHOW' || $type == 'DESCRIBE' || $type == 'EXPLAIN')

{

 

$go = mysql_query($query,$con) die (mysql_error() . ": " . $query); // Run the query, or echo the error.

$fetch = array();

while($fetch[] = mysql_fetch_array($go));                                    // Take the output and create array $var[]

$num = mysql_num_rows($go);                                        // Count the number of affected rows.

 

$array = array

(

'query' => $query, // $var['query'] returns the query.

'num' => $num,    // $var['num'] returns the number of affected rows.

'fetch' => $fetch  // $var['fetch'] returns the results of the query.

        );

 

}

 

All the best

 

Keith

[roopurt18]

While kickstart is correct in his altered version of your original function, I do not recommend doing that.

 

The reason being that you will end up looping over your result-set an extra time.  For small result sets this is no big deal.  For large result sets it becomes a bottleneck in your script.

[bundyxc]

Oops, I meant to type this code. Now I get what he was saying:

 

<?php

while ( $row = $var['fetch'])
{
   echo $row['firstName'] . $row['lastName'];
}

?>

 

Would that work?

 

Not really. You would need to modify your original function. Something like this:-

 

if ( $type == 'SELECT' || $type == 'SHOW' || $type == 'DESCRIBE' || $type == 'EXPLAIN')

{

 

$go = mysql_query($query,$con) die (mysql_error() . ": " . $query); // Run the query, or echo the error.

$fetch = array();

while($fetch[] = mysql_fetch_array($go));                                    // Take the output and create array $var[]

$num = mysql_num_rows($go);                                        // Count the number of affected rows.

 

$array = array

(

'query' => $query, // $var['query'] returns the query.

'num' => $num,    // $var['num'] returns the number of affected rows.

'fetch' => $fetch  // $var['fetch'] returns the results of the query.

        );

 

}

 

All the best

 

Keith

 

This all makes sense to me, and I understand now why the code wouldn't work to begin with.. but now I'm curious. Why did we have to declare the variable '$fetch = array();' if we were about to redefine it in the next line? I may be missing something.

[roopurt18]

It's not redefining it.  The [] operator causes a value to be pushed onto an already defined array.

 

It just so happens that if the array is not defined to begin with, PHP will create an empty one for you.  Relying on the language to initialize variables for you is a dangerous habit to fall into.  It allows you to be a lazy programmer and lazy programmers often make more mistakes.  They also have many more problems moving their script from one version of a language to another because this default behavior is not set in stone and can change at any time.

 

And I'll repeat, I do not recommend pre-building the array in that manner there.

[bundyxc]

While kickstart is correct in his altered version of your original function, I do not recommend doing that.

 

The reason being that you will end up looping over your result-set an extra time.  For small result sets this is no big deal.  For large result sets it becomes a bottleneck in your script.

 

Sorry roopurt, I hadn't read this. I'm curious though, what do you mean by 'looping over the result-set'? It sounds like you're saying that I'd be fetching the array from the query twice, but I don't know. Just an assumption. Thanks for all of your help. I'm just trying to learn, and it's nice to be able to learn from humans. I read up and get all that I can out of the manual, but it's nice to get explanations that are geared toward people who don't know what the hell they're talking about. haha.

[roopurt18]

In mathematical terms a set is a collection of items and an element is an item in the set.

 

I could say:

Let C be the set of all the items currently on my desk.

 

Some of the elements in that set would be: a cell phone, a yellow high lighter, a quiznos cup, telephone, etc.

 

When I use the term result-set, I mean the resulting set (or group) of records returned by the database query.

 

Each element in the query result-set is a single database record (and each record is a collection (or set) of columns).

 

 

When you do something like:

$q = mysql_query( "select ..." );
// $q now contains a handle, or pointer, or reference to the RESULT-SET
// mysql_fetch_assoc() returns the next ELEMENT from the result set.
// Therefore if we want to loop over the result-set, we use:
while( $row = mysql_fetch_assoc( $q ) ) {
  print_r( $row );
}

 

This is why your code was wrong before.  You were running the query and assigned the result-set to $go.  Then you were calling mysql_fetch_*() and assigning the result to $fetch.  In that case $fetch is a single row, and not the entire result-set.  You were then returning $fetch back to the user.

 

What kickstart is saying is you could do something like this in your function:

if ( $type == 'SELECT' || $type == 'SHOW' || $type == 'DESCRIBE' || $type == 'EXPLAIN')
   {
   
   $go = mysql_query($query,$con) die (mysql_error() . ": " . $query); // Run the query, or echo the error.
   $fetch = array();
   while($fetch[] = mysql_fetch_array($go));                                    // Take the output and create array $var[]
   // LOOK HERE!!!!!!
   // The above two lines loop over the entire result set ONE TIME



$num = mysql_num_rows($go);                                         // Count the number of affected rows.
   
   $array = array
      (
         'query' => $query, // $var['query'] returns the query.
         'num' => $num,     // $var['num'] returns the number of affected rows.

        // HERE YOU ARE ASSIGNING THE ARRAY YOU BUILT ABOVE (WHEN YOU LOOPED OVER THE RESULT-SET)
         'fetch' => $fetch  // $var['fetch'] returns the results of the query.
        ); 

 

So you could use your function like this:

$result = pure_query( 'select', "SELECT * FROM ..." ); // REMEMBER THAT pure_query LOOPS OVER THE RESULT-SET ONCE ITSELF
while( $row = $result['fetch'] ) { // AND NOW YOU ARE LOOPING OVER IT AGAIN, WASTE OF RESOURCE ON LARGE RESULT-SETS
}

 

My recommendation is to do the following:

if ( $type == 'SELECT' || $type == 'SHOW' || $type == 'DESCRIBE' || $type == 'EXPLAIN')
   {
   
   $go = mysql_query($query,$con) die (mysql_error() . ": " . $query); // Run the query, or echo the error.
   $num = mysql_num_rows($go);                                         // Count the number of affected rows.
   
   $array = array
      (
         'query' => $query, // $var['query'] returns the query.
         'num' => $num,     // $var['num'] returns the number of affected rows.
         // NOTICE I SET EQUAL TO $GO
         'fetch' => $go  // $var['fetch'] returns the results of the mysql_query(), which is the RESULT-SET
        ); 

 

So you could use your function like this:

$result = pure_query( 'select', "SELECT * FROM ..." ); // pure_query no longer loops over the result set
while( $row = mysql_fetch_assoc( $result['fetch'] ) ) { // but we have to use mysql_fetch_assoc() here
}

 

[kickstart]

While kickstart is correct in his altered version of your original function, I do not recommend doing that.

 

The reason being that you will end up looping over your result-set an extra time.  For small result sets this is no big deal.  For large result sets it becomes a bottleneck in your script.

 

I fully agree. I do not see a reason for this function, but I did it to explain one very basic problem with the original function.

 

All the best

 

Keith