Simple MySQL Function

[PCHomepage]

I am trying to create a basic PHP function for retrieving MySQL values which seems easier than repeating several lines of code on each page but it does not pull up results for some reason. Is this even possible and if so, does anyone see what I am doing wrong?

The included common.php contains all the database values except for the query itself.

<?php
include_once $_SERVER ['DOCUMENT_ROOT'] . "/functions/config.php";

function DBConnect($query, $dbname) {
	$connection = mysql_connect($dbhost, $dbusername, $dbpass);
	$resultCat = mysql_db_query($dbname, $query);
	if (!$query) {
		echo "No Query was entered";
		exit;
	}
		return mysql_fetch_array($resultCat);
}
?>

 

[vacunita]

Number one, and straight from the PHP online manual:

mysql_db_query
This function is deprecated, do not use this function. Use mysql_select_db() and mysql_query() instead.

In other words, the function has been replaced, and there is no guarantee future versions of PHP will support it, so it should not be used.

Second:

You should be getting errors:

Your variables for the db connection are not defined anywhere in your function. This is called scope. The variables that may come from the config.php file that is included are in a different scope to that of the inside of the function.

You would need to either pass the values explicitly to the function or include your config.php file inside the function.

Then should you not be checking that the query variable has something before executing the query rather than after?

And finally, if your query is returning more than one row, your function will only return the first one.

However it returns an array, so it can't just simply be echoed.




----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[PCHomepage]

Thanks for the quick reply! My apologies for the ignorant code. I am quite spoiled from having used an IDE for years with its own built-in functions and am trying to re-learn to do manually (outside the IDE) what I once knew! As for mysql_db_query(), I knew that had been depreciated but accidentally pasted in an old copy of the code.

You're right, the included file must go inside the function so that the variables are within the same scope. I have moved it there. Actually it was giving an error but not one that suggested the problem was in the scope so I had missed that.

You're also right that the query conditional was in the wrong place. That was very sloppy of me.

Basically this function is to be used for only one row at a time. However, is there some way to use it for multiple rows if needed? Can it be used for updates and inserts too?

 

[vacunita]

You can make the function produce an array with the results, and return that when there is more than one row.

And you can also code it to just return a true or false for updates and inserts depending on the results.






----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[PCHomepage]

The function is working fine now for fetching a single entry but I am still unsure how to proceed in getting multiple entries or for updating and inserting. Here's what I have so far:

<?php
function DBConnect($query) {
	if (!$query) {
		echo "No Query was entered";
		exit;
	}
	include "config.php";
	mysql_connect($dbhost, $dbusername, $dbpass);
	mysql_select_db($dbname);
	$resultCat = mysql_query($query);
	$numrowsCat = mysql_num_rows ($resultCat);
    return mysql_fetch_array($resultCat);
}
?>

Or if you don't want to include an extra file, the values can be here too:

<?php
function DBConnect($query) {
	if (!$query) {
		echo "No Query was entered";
		exit;
	}
        $dbhost = "localhost";
        $dbusername = "user";
        $dbpass = "password";
        $dbname = "database_name";
	mysql_connect($dbhost, $dbusername, $dbpass);
	mysql_select_db($dbname);
	$resultCat = mysql_query($query);
	$numrowsCat = mysql_num_rows ($resultCat);
    return mysql_fetch_array($resultCat);
}
?>

The $numrowsCat is used with a FOR loop ( ie: for ($i = 0; $i < $numrowsCat; $i++) { } etc.) for getting multiple values in the original code before this function was created but I'm not sure what to do in order to make it work generically! That is, the FOR loop needed to have field names and HTML in it in order to work.

 

[vacunita]

Try something like this for multiple rows:

function DBConnect($query) {
    if (!$query) {
        echo "No Query was entered";
        exit;
    }
    include "config.php";
    mysql_connect($dbhost, $dbusername, $dbpass);
    mysql_select_db($dbname);
    $resultCat = mysql_query($query);
    $numrowsCat = mysql_num_rows ($resultCat);
   [red] if($numrowsCat>1){
     $results_array=array();
     while($row=mysql_fetch_array($resultCat)){
          $results_array[]=$row;
}

     return $results_array();

}
   else{
   return mysql_fetch_array($resultCat);
   }[/red]
}

With that it will return an array with the rows from the query, than can be accessed and manipulated normally.

<?PHP
$rows=DBconnect("SELECT *FROM mytable");

for($i=0;$i<=count($rows);$i++){
echo $row[$i]['name_of_field'];
}




?>

----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[PCHomepage]

Thanks! I'll give the multiple row code a try shortly.

In the meantime, the function has been working well for selecting, updating, inserting and deleting single rows. Until now. I have an instance where it won't insert or update and I am pulling my hair out as it does not give any errors either! There is an error when the query string is missing but how can I make it give a MySQL error if anything else is wrong? I am hoping that error output might help solve the current issue.

Here is what I have now:

function DBConnect($query, $ActionType) {
	if (!$query) {
		echo "No Query was entered";
		exit;
	}
	include "config.php";
	mysql_connect($dbhost, $dbusername, $dbpass);
	mysql_select_db($dbname);
	$resultCat = mysql_query($query);
	if ($ActionType == "Select") {
		return mysql_fetch_array($resultCat);
	} else {
		return true;
	}
}

 

[lgarner]

You could add some debugging statements. See

http://us.php.net/manual/en/function.mysql-select-db.php

for examples on catching errors in the mysql_connect and mysql_select_db funtions.

 

[PCHomepage]

The error correction found the problem right away: a misspelled field name. That fixed, it is now working as it should for single rows. However, there was apparently a typo on the last return since it gave an error Fatal error: Function name must be a string on that line. Removing the parenthesis seems to have solved it.

function DBConnect($query, $ActionType) {
	if (!$query) {
		echo "No Query was entered";
		exit;
	}
	include "config.php";
	mysql_connect($dbhost, $dbusername, $dbpass);
	mysql_select_db($dbname);
	$resultCat = mysql_query($query);
	if (mysql_error()) {
		echo mysql_error();
		exit;
	}
	if ($ActionType == "Select") {
		return mysql_fetch_array($resultCat);
	} elseif ($ActionType == "Delete" || $ActionType == "Insert") {
		return true;
	} elseif ($ActionType == "Multiple") {
		$numrowsCat = mysql_num_rows($resultCat);
		if ($numrowsCat > 1) {
			$results_array = array();
			while($row = mysql_fetch_array($resultCat)) {
				$results_array[] = $row;
			}
		   return $results_array;
		}
	}
}

It's being called like this:

$queryCountry = "SELECT ID, CountryName FROM countries ORDER BY CountryName ASC";
$resultCountry = DBConnect($queryCountry, "Multiple");

... and to use it for a list box, for example, like this:

Country: <select name="Country">

                  <option value="">Select Country</option>

		<?php for ($i=0;$i<=count($resultCountry);$i++) {
			if ($rowGuestbook['Country'] == $resultCountry[$i]['ID']) {
				$Selected = " SELECTED";
			} else {
				$Selected = "";
			}
			echo '<option value="'.trim($resultCountry[$i]['ID']).'"'.$Selected.'>'.trim($resultCountry[$i]['CountryName']).'</option>';
		}
		?></select>

... or to delete or insert a record (delete shown):

$queryGuestbook = "DELETE FROM guestbook WHERE ID=".$_POST['ID'];
DBConnect($queryGuestbook, "Delete");

.. or to select a single record:

$queryGuestbook = "SELECT * FROM guestbook WHERE ID=".$_POST['ID'];
$rowGuestbook = DBConnect($queryGuestbook, "Select");

... to use the values:

echo $rowGuestbook['FieldName'];

Thanks for all the help and I hope someone can make use of this themselves!

 

[PCHomepage]

I discovered a tiny bug in the function where, when getting multiple entries, it pulls up and extra empty one. I am not sure if the function itself can be fixed to prevent it but a simple workaround is to subtract 1 from the row count. The example I provided for the list box would then be:

Country: <select name="Country">

<option value="">Select Country</option>

<?php

for ($i=0;$i<=count($resultCountry)-1;$i++) {
   echo '<option value="'.trim($resultCountry[$i]['ID']).'>'.trim($resultCountry[$i]['CountryName']).'</option>';
}
?>

If anyone has a fix for the function itself, I would like to hear about it. Thanks!

 

[johnwm]

The 'count' function returns the number of records in the set. To iterate through them in a zero-based system you need to count from <zero to (count-1)>, or from <1 to count>. For instance if you have 4 records then count will be 4, but iterating from 0 to 4 gives 5 items.

If you want the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller

 

[PCHomepage]

Thanks, that makes sense. Would that mean that $i=1 would have the same result as $i<=count($resultCountry)-1?

 

[johnwm]

No - because you are the $i as an index into a zero-based array.

If you want the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller