Solved

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

Posted on 2011-09-09
28
293 Views
Last Modified: 2012-06-27
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
Comment
Question by:h3rm1t9536
  • 12
  • 9
  • 6
  • +1
28 Comments
 
LVL 30

Expert Comment

by:Marco Gasi
ID: 36510846
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
 
LVL 3

Accepted Solution

by:
woepwobin earned 500 total points
ID: 36511116
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
 

Author Comment

by:h3rm1t9536
ID: 36511150
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
 
LVL 3

Expert Comment

by:woepwobin
ID: 36511213
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
 

Author Comment

by:h3rm1t9536
ID: 36511255
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
 
LVL 30

Expert Comment

by:Marco Gasi
ID: 36512260
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
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 36516236
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
 

Author Comment

by:h3rm1t9536
ID: 36522653
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
 
LVL 30

Expert Comment

by:Marco Gasi
ID: 36522726
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
 
LVL 3

Expert Comment

by:woepwobin
ID: 36522778
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
 

Author Comment

by:h3rm1t9536
ID: 36522984
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
 
LVL 30

Expert Comment

by:Marco Gasi
ID: 36523019
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
 

Author Comment

by:h3rm1t9536
ID: 36523083
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
 
LVL 3

Expert Comment

by:woepwobin
ID: 36523092
Ah sorry, add ';' to the end of the 'die' statement.
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

Author Comment

by:h3rm1t9536
ID: 36523109
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
 
LVL 3

Expert Comment

by:woepwobin
ID: 36523182
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
 

Author Comment

by:h3rm1t9536
ID: 36523277
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
 
LVL 30

Expert Comment

by:Marco Gasi
ID: 36523331
Why not

require_once ('C:XAMPP/mysqli_connect.php');
$country = array();
$country = getAllValues('CountryID', 'CountryValue', 'country');
mysqli_close($dbc); // Close the database connection.
0
 

Author Comment

by:h3rm1t9536
ID: 36523333
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
 

Author Comment

by:h3rm1t9536
ID: 36523542
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
 
LVL 3

Expert Comment

by:woepwobin
ID: 36523660
function getAllValues($id, $value, $table)
{
  global $dbc;
...

Open in new window

0
 

Author Comment

by:h3rm1t9536
ID: 36523725
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
 
LVL 3

Expert Comment

by:woepwobin
ID: 36523799
Can you post the code you have now? Too much has changed for me to make soup from now.
0
 

Author Comment

by:h3rm1t9536
ID: 36523902
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
 

Author Comment

by:h3rm1t9536
ID: 36528767
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
 
LVL 3

Expert Comment

by:woepwobin
ID: 36528895
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
 
LVL 3

Expert Comment

by:woepwobin
ID: 36528910
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
 
LVL 30

Expert Comment

by:Marco Gasi
ID: 36595037
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

If your site has a few sections that need to be secure when data is transmitted between the server and local computer, such as a /order/ section for ordering or /customer/ which contains customer data, etc it would of course be recommended to secure…
Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
The viewer will learn the basics of jQuery including how to code hide show and toggles. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now