Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 321
  • Last Modified:

PHP function to select data from MySQL DB and put into an array

Hi There!

I'm trying to build a web portal that connects to a DB I created. I am busy trying to create a function writen in php. The function is to open a connection to the databse, select the values from the table (including two fields - the ID and the value).

Once the values are available I would like to put them into an array (that is the name the respective table).

The function so far works in that it can access the database, it can also get all value. The only issue is that I cant manage to create a while loop that will place the key and the value into a new array that is named after the respective table.



Thanks so much in advanced! Chelsea

 
function createArray($id, $value, $table)
{
	require_once ('C:XAMPP/mysqli_connect.php'); // Connect to the db.
	
	// Make the query:
	$q = 'SELECT '. $id .', '. $value .' FROM '. $table .' ORDER BY '. $value .' ASC';		
	$r = @mysqli_query ($dbc, $q); // Run the query.

	// Fetch and print all the records:
	while ($row = mysqli_fetch_array($r, MYSQLI_ASSOC)) 
	{
		$table[$row[$id]]=$row[$value];

		//for testing 
                //echo $row[$id];
		//for testing
                //echo $row[$value];
	}
	
	mysqli_free_result ($r); // Free up the resources.	
	mysqli_close($dbc); // Close the database connection.
	
	return $table;
}
 $country1 = createArray("CountryID", "CountryValue", "country");

Open in new window

0
h3rm1t9536
Asked:
h3rm1t9536
  • 12
  • 9
  • 6
  • +1
1 Solution
 
Marco GasiFreelancerCommented:
You can use this:

function getAllValues($sql, $show_error=0){
    if (!isset($sql) || $sql == ''){
        echo "Can't execute the query: please, verify it.";
        return false;
    }
    $result = @mysql_query($sql);
    if (@mysql_num_rows($result)){
        $resultSet = array();
        $keys = array();
        $values = array();
        while ($row = mysql_fetch_assoc($result)){
            foreach ($row as $k=>$v){
                if ($v != ''){
                    $keys[] = $k;
                    $values[] = $v;
                }
            }
            for ($x=0;$x<count($keys);$x++){
                $partialResult[$keys[$x]] = $values[$x];
            }
            $resultSet[] = $partialResult;
        }
        return $resultSet;
    }else{
        if ($show_error == 1){
                echo mysql_error()."<br>";
                exit();
        }
    }
}

Open in new window


$sql='SELECT username FROM usertable';
$array = getAllValues($sql);

foreach ($array as $key=>$value){
  echo "$key = $value<br />";
}

Cheers
0
 
woepwobinCommented:
First you're using $table as a string:
$q = 'SELECT '. $id .', '. $value .' FROM '. $table .' ORDER BY '. $value .' ASC';

Open in new window


and then you're trying to use it as an array:
$table[$row[$id]]=$row[$value];

Open in new window



So either create an empty variable for the table and use that:
$newtable[$row[$id]]=$row[$value];

Open in new window


or use $table to name the new variable:
$$table[$row[$id]]=$row[$value];

Open in new window

which, if $table would be 'mytable' would create the variable $mytable.
0
 
h3rm1t9536Author Commented:
thanks so much! ... can I ask one question...

How do I put convert the new array returned ($resultSet[]) it into a javascript array... the reason is I need to use the arrays created from this function in another function - that function is done using javascript

In the code below the array 'country' will be the array generated from the function you explained above.

I did try add the array by putting it in
<?php ?>

Open in new window

tags - but that didnt work...

<script type="text/javascript">
var obj = new actb(document.getElementById('textbox_id'), country);</script>

Open in new window

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
woepwobinCommented:
Something like this:

<script type="text/javascript">
var table = new Array();
<?php
  foreach ($resultSet as $id=>$value)
    echo "table[$id] = '$value';\n";
?>
</script>

Open in new window

0
 
h3rm1t9536Author Commented:
Sorry for the initial question about getting the function to work ... i tried the code below and it still doesn't work...


function createArray($k, $v, $table)
{
	require_once ('C:XAMPP/mysqli_connect.php'); // Connect to the db.
	
	// Make the query:
	$q = 'SELECT '. $k .', '. $v .' FROM '. $table .' ORDER BY '. $v .' ASC';		
	$r = @mysqli_query ($dbc, $q); // Run the query.

	$resultSet = array();
	// Fetch and print all the records:
	while ($row = mysqli_fetch_array($r, MYSQLI_ASSOC)) 
	{
		$resultSet[$row[$id]]=$row[$value];
	}
	
	mysqli_free_result ($r); // Free up the resources.	
	mysqli_close($dbc); // Close the database connection.
	
	echo $resultSet[2];
	return $resultSet;
}
 $country[] = createArray("CountryID", "CountryValue", "country");

Open in new window

0
 
Marco GasiFreelancerCommented:
Use my function building the query externally:

$q = 'SELECT CountryID, CountryValue FROM country ORDER BY CountryValue ASC';
$country = getAllValues($q);

I repost the function because in the first version all records with empty value was skipped (for my needs when I wrote the function): here I post a version which processes all records.

Cheers
function getAllValues($sql, $show_error=0){
    if (!isset($sql) || $sql == ''){
        echo "Can't execute the query: please, verify it.";
        return false;
    }
    $result = @mysql_query($sql);
    if (@mysql_num_rows($result)){
        $resultSet = array();
        $keys = array();
        $values = array();
        while ($row = mysql_fetch_assoc($result)){
            if ($v != ''){
                $keys[] = $k;
                $values[] = $v;
            }
            for ($x=0;$x<count($keys);$x++){
                $partialResult[$keys[$x]] = $values[$x];
            }
            $resultSet[] = $partialResult;
        }
        return $resultSet;
    }else{
        if ($show_error == 1){
                echo mysql_error()."<br>";
                exit();
        }
    }
}

Open in new window

0
 
Ray PaseurCommented:
Very readable with excellent examples.  It will help you understand the relationship between PHP and MySQL.
http://www.sitepoint.com/books/phpmysql4/

PHP also has some introductory information here.  Probably worth reading, but the SitePoint book will really get you a good foundation in PHP and MySQL.
http://us2.php.net/tut.php
0
 
h3rm1t9536Author Commented:
Thanks Marqus Woepwobin, and Ray! The function is only working if used once - if the function is used more than once the errors generated are:

Warning: mysqli_num_rows() expects parameter 1 to be mysqli_result, null given in C:\xampp\htdocs\isd\php_functions\getAllValues.php on line 10

Warning: mysqli_free_result() expects parameter 1 to be mysqli_result, null given in C:\xampp\htdocs\isd\php_functions\getAllValues.php on line 29

Warning: mysqli_free_result() expects parameter 1 to be mysqli_result, null given in C:\xampp\htdocs\isd\php_functions\getAllValues.php on line 30

Notice: Undefined variable: dbc in C:\xampp\htdocs\isd\php_functions\getAllValues.php on line 31

Warning: mysqli_close() expects parameter 1 to be mysqli, null given in C:\xampp\htdocs\isd\php_functions\getAllValues.php on line 31

Do you what I have done wrong with the closing functions (mysqli_free_result() and mysqli_close())?

Thanks so much for all your guys help!


<?php
function getAllValues($id, $value, $table)
{
	$sql = 'SELECT ' . $id . ' as k, ' . $value . ' as v FROM ' . $table . ' ORDER BY ' . $value . ' ASC';
	
	require_once ('C:XAMPP/mysqli_connect.php'); // Connect to the db.
    $result  = @mysqli_query ($dbc, $sql); // Run the query.

	// Count the number of returned rows:
	$num = mysqli_num_rows($result);
	
    if ($num > 0)
	{// If it ran OK, add the records
	
		// Print how many records there are:
		echo "<h3>There are currently $num records in country.</h3>\n";
		
        $resultSet = array();
        $keys = array();
        $values = array();
        while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC))
		{
			$keys= $row['k'];
			$values= $row['v'];
			$resultSet[$keys] = $values;
        }
	    return $resultSet;
    }
		mysqli_free_result ($result); // Free up the resources.
		mysqli_free_result ($num); // Free up the resources.
		mysqli_close($dbc); // Close the database connection.
}
?>

Open in new window

0
 
Marco GasiFreelancerCommented:
The original function works fine every time I call it: why you don't want to use it as it is? Anyway, can you show how you call the function? How you call it the first time and the second time?

btw, I have to highlight that functions should only return a result, not print it: it would be  better to use a function to get the number of records and a function to get a data array and manage their output externally:

//call the function to retrieve the number of records
$num = getNumberOfRecords();
if ($num<> '' && $num > 0){
  //print the title
  echo "<h3>There are currently $num records in country.</h3>\n";
}else{
  echo "Error";
}

//get records
$q = 'SELECT CountryID, CountryValue FROM country ORDER BY CountryValue ASC';
$country = getAllValues($q);

//print records
foreach ($country as $value){
  echgo "$value<br />";
}

Cheers
0
 
woepwobinCommented:
Looks like mysqli_query throws an error the second time.

You can change this:

$result  = @mysqli_query ($dbc, $sql); // Run the query.

Open in new window

into this:

if (!($result  = @mysqli_query ($dbc, $sql)))
  die(mysql_error($dbc))

Open in new window


to display the error.
0
 
h3rm1t9536Author Commented:
The function will not print anything  - that is just used for testing purposes. I will take it out now - incase that is causing a problem.

I havent used your code exactly because it didnt work - I had to alter it just a bit - but the core is still the same...

Basically all I want the function to do it create an array where the key stores the ID and the value stores the actual value in the table. If possible I would also like the function to return a javascript array not a php array. However I do have code that works - its just not in a function ( hence the code is repeated unnecessarily)

Sorry if I did not explain myself correctly earlier...

How I call the function is by:
$country = array();
$country = getAllValues('CountryID', 'CountryValue', 'country');

$strategic_application = array();
$strategic_application = getAllValues('StrategicApplicationID', 'StrategicApplicationValue', 'strategic_application');

Open in new window

0
 
Marco GasiFreelancerCommented:
Try woepwobin suggestion to see if is a mysqli error, but I don't understand why my function didn't work since I 've used it tenth of times...
0
 
h3rm1t9536Author Commented:
woepwobin - thanks for the suggestion - the following error is produced:

Parse error: syntax error, unexpected T_VARIABLE in C:\xampp\htdocs\isd\php_functions\getAllValues.php on line 13



0
 
woepwobinCommented:
Ah sorry, add ';' to the end of the 'die' statement.
0
 
h3rm1t9536Author Commented:
the following errors are now given:

Notice: Undefined variable: dbc in C:\xampp\htdocs\isd\php_functions\getAllValues.php on line 9

Warning: mysql_error() expects parameter 1 to be resource, null given in C:\xampp\htdocs\isd\php_functions\getAllValues.php on line 9
0
 
woepwobinCommented:
The resource in $dbc is defined in 'C:XAMPP/mysqli_connect.php', but that is included only once, and you're closing it after the first query.

Try moving the mysqli_close($dbc) to the end of the file, or (bad practice) change the 'require_once' into 'require'.
0
 
h3rm1t9536Author Commented:
Thanks Woepwobin! It works - at last! But it is producing the folling errors:

Notice: Constant DB_USER already defined in C:\xampp\mysqli_connect.php on line 3

Notice: Constant DB_PASSWORD already defined in C:\xampp\mysqli_connect.php on line 4

Notice: Constant DB_HOST already defined in C:\xampp\mysqli_connect.php on line 5

Notice: Constant DB_NAME already defined in C:\xampp\mysqli_connect.php on line 6

Is there any way I could use require_once in the main code when I call the getAllValues() and then place the mysqli_close()  after I have called the getAllValues()?
0
 
Marco GasiFreelancerCommented:
Why not

require_once ('C:XAMPP/mysqli_connect.php');
$country = array();
$country = getAllValues('CountryID', 'CountryValue', 'country');
mysqli_close($dbc); // Close the database connection.
0
 
h3rm1t9536Author Commented:
Sorry I forgot to mention what code I changed exactly - It works with the exception of the above errors when I change require_once() to require() ... hence the question above as to what other solution could work that wouldn't exhaust the server as much...

Thanks so much for your help again!
0
 
h3rm1t9536Author Commented:
Sorry Marqus - that doesn't work - it returns the following errors:

Warning: mysqli_num_rows() expects parameter 1 to be mysqli_result, null given in C:\xampp\htdocs\isd\php_functions\getAllValues.php on line 11

Warning: mysqli_free_result() expects parameter 1 to be mysqli_result, null given in C:\xampp\htdocs\isd\php_functions\getAllValues.php on line 30

Warning: mysqli_free_result() expects parameter 1 to be mysqli_result, null given in C:\xampp\htdocs\isd\php_functions\getAllValues.php on line 31

Warning: mysqli_num_rows() expects parameter 1 to be mysqli_result, null given in C:\xampp\htdocs\isd\php_functions\getAllValues.php on line 11

Warning: mysqli_free_result() expects parameter 1 to be mysqli_result, null given in C:\xampp\htdocs\isd\php_functions\getAllValues.php on line 30

Warning: mysqli_free_result() expects parameter 1 to be mysqli_result, null given in C:\xampp\htdocs\isd\php_functions\getAllValues.php on line 31
0
 
woepwobinCommented:
function getAllValues($id, $value, $table)
{
  global $dbc;
...

Open in new window

0
 
h3rm1t9536Author Commented:
I get the following errors will that line woepwobin...

Warning: mysqli_num_rows() expects parameter 1 to be mysqli_result, null given in C:\xampp\htdocs\isd\php_functions\getAllValues.php on line 11

Warning: mysqli_free_result() expects parameter 1 to be mysqli_result, null given in C:\xampp\htdocs\isd\php_functions\getAllValues.php on line 30

Warning: mysqli_free_result() expects parameter 1 to be mysqli_result, null given in C:\xampp\htdocs\isd\php_functions\getAllValues.php on line 31

Warning: mysqli_num_rows() expects parameter 1 to be mysqli_result, null given in C:\xampp\htdocs\isd\php_functions\getAllValues.php on line 11

Warning: mysqli_free_result() expects parameter 1 to be mysqli_result, null given in C:\xampp\htdocs\isd\php_functions\getAllValues.php on line 30

Warning: mysqli_free_result() expects parameter 1 to be mysqli_result, null given in C:\xampp\htdocs\isd\php_functions\getAllValues.php on line 31
0
 
woepwobinCommented:
Can you post the code you have now? Too much has changed for me to make soup from now.
0
 
h3rm1t9536Author Commented:
haha! thanks for still trying to help out!

The function:

 
<?php
function getAllValues($id, $value, $table)
{
	$sql = 'SELECT ' . $id . ' as k, ' . $value . ' as v FROM ' . $table . ' ORDER BY ' . $value . ' ASC';
	
	
	require ('C:XAMPP/mysqli_connect.php'); // Connect to the db.
    $result  = @mysqli_query ($dbc, $sql); // Run the query.

	// Count the number of returned rows:
	$num = mysqli_num_rows($result);
	
    if ($num > 0)
	{// If it ran OK, add the records
		
        $resultSet = array();
        $keys = array();
        $values = array();
        while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC))
		{
			$keys= $row['k'];
			$values= $row['v'];
			$resultSet[$keys] = $values;
        }
	    return $resultSet;
    }
		mysqli_free_result ($result); // Free up the resources.
		mysqli_free_result ($num); // Free up the resources.
		mysqli_close($dbc);  //Close the database connection.
}
?>

Open in new window




The code to call the function:


 
include('php_functions/getAllValues.php');

$country = array();
$country = getAllValues('CountryID', 'CountryValue', 'country');

$strategic_application = array();
$strategic_application = getAllValues('StrategicApplicationID', 'StrategicApplicationValue', 'strategic_application');

Open in new window


The error using require_once():

Notice: Constant DB_USER already defined in C:\xampp\mysqli_connect.php on line 3

Notice: Constant DB_PASSWORD already defined in C:\xampp\mysqli_connect.php on line 4

Notice: Constant DB_HOST already defined in C:\xampp\mysqli_connect.php on line 5

Notice: Constant DB_NAME already defined in C:\xampp\mysqli_connect.php on line 6

The error using gobal():

Warning: mysqli_num_rows() expects parameter 1 to be mysqli_result, null given in C:\xampp\htdocs\isd\php_functions\getAllValues.php on line 11

Warning: mysqli_free_result() expects parameter 1 to be mysqli_result, null given in C:\xampp\htdocs\isd\php_functions\getAllValues.php on line 30

Warning: mysqli_free_result() expects parameter 1 to be mysqli_result, null given in C:\xampp\htdocs\isd\php_functions\getAllValues.php on line 31

Warning: mysqli_num_rows() expects parameter 1 to be mysqli_result, null given in C:\xampp\htdocs\isd\php_functions\getAllValues.php on line 11

Warning: mysqli_free_result() expects parameter 1 to be mysqli_result, null given in C:\xampp\htdocs\isd\php_functions\getAllValues.php on line 30

Warning: mysqli_free_result() expects parameter 1 to be mysqli_result, null given in C:\xampp\htdocs\isd\php_functions\getAllValues.php on line 31
0
 
h3rm1t9536Author Commented:
Hi did you manage to take a look at the code above Woepwobin?

You know the code you gave me for the converting a php array into javascipt array? does it only work with strings? as its retunring errors when the values in the php array are numbers...
0
 
woepwobinCommented:
Please try this for php_functions/getAllValues.php:

function getAllValues($dbs, $id, $value, $table)
{
  $sql = 'SELECT ' . $id . ' as k, ' . $value . ' as v FROM ' . $table . ' ORDER BY ' . $value . ' ASC';
  if (!($result  = @mysqli_query ($dbc, $sql)))
    die(mysqli_error($dbc));

  // Count the number of returned rows:
  $num = mysqli_num_rows($result);
        
  if ($num > 0)
  {// If it ran OK, add the records
    $resultSet = array();
    $keys = array();
    $values = array();
    while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC))
    {
      $keys= $row['k'];
      $values= $row['v'];
      $resultSet[$keys] = $values;
    }
    return $resultSet;
  }
}
?>

Open in new window


And this for the other part:
<?php
require_once 'C:XAMPP/mysqli_connect.php');

include('php_functions/getAllValues.php');

$country = array();
$country = getAllValues($dbc, 'CountryID', 'CountryValue', 'country');

$strategic_application = array();
$strategic_application = getAllValues($dbc, 'StrategicApplicationID', 'StrategicApplicationValue', 'strategic_application');

mysqli_free_result ($result); // Free up the resources.
mysqli_free_result ($num); // Free up the resources.
mysqli_close($dbc);  //Close the database connection.
?>

Open in new window

0
 
woepwobinCommented:
Notice my error in function getAllValues:

function getAllValues($dbs, $id, $value, $table)

Open in new window

should be:
function getAllValues($dbc, $id, $value, $table)

Open in new window


($dbs vs $dbc)
0
 
Marco GasiFreelancerCommented:
I doesn't your decision, but I think you should split points at least between me and woepwobin since the skeleton of the function you use was provided by me. Anyway, good luck with your project.
Cheers
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

  • 12
  • 9
  • 6
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now