# How do you usually construct an array from several MySQL tables?

**URL:** <https://forum.kirupa.com/t/how-do-you-usually-construct-an-array-from-several-mysql-tables/300205>\
**Category:** programming\
**Created:** [November 17, 2009, 5:25pm UTC](https://forum.kirupa.com/t/how-do-you-usually-construct-an-array-from-several-mysql-tables/300205 "2009-11-17T17:25:18Z")\
**Posts on this page:** 1\
**Page:** 1

<div class="post-metadata">

**Author:** ![imagined](https://avatars.discourse-cdn.com/v4/letter/i/a87d85/32.png) [@imagined](https://forum.kirupa.com/u/imagined)\
**Post date:** [November 17, 2009, 5:25pm UTC](https://forum.kirupa.com/t/how-do-you-usually-construct-an-array-from-several-mysql-tables/300205/1 "2009-11-17T17:25:18Z")

</div>

I’m building an array from several MySQL tables. I have done it two ways but I’m not completely satisfied and I would like your opinion on how you would do it.

Those 3 values should be gathered from 3 different tables.  
**Table WorkProvided**  
[LIST]  
[_]start\_date  
[_]end\_date  
[_]clientID  
[_]serviceID  
[/LIST]

**Table Clients**  
[LIST]  
[_]clientID  
[_]clientName  
[/LIST]

**Table Services**  
[LIST]  
[_]serviceID  
[_]serviceName  
[/LIST]

Let’s say I need an array with 3 values:  
[0]=start\_date  
[1]=end\_date  
[2]=client name and the service name

So if i make a query to WorkProvided, I get the clientID and serviceID, but I need that [2] contains the actual name of the client and service name.

I usually make 3 arrays: workprovided, clients, services. and then do a foreach loop where i match the clientID, and added to the array, and match the serviceID and match it to the array.

I also have a function in progress

```php
function createArrayFromMySQL($tablename, $columns){
	$array = array();
	$data_array = array();

	// analyze columns and make sure they exists
	
	$i=1;
	// get number of results, and create a for loop $i<$number_of_results

	$query = 'SELECT * FROM '.$tablename;
	$result = mysql_query($query);
	while($row = mysql_fetch_array($result)){
		foreach($columns as $key_cols => $value_cols){
			if(is_array($value_cols)){
				$table =	$value_cols[0];
				$match =	$value_cols[1];
				$incoming =	$value_cols[2];
				$query_{$key_cols} = 'SELECT * FROM '.$table;

				// find a way to only do the repetitive queries once
				// maybe save results into a dynamically generated array
				// if you echo the following, you'll see what i mean
				// echo $query_{$key_cols}.'<br />';

				$result_{$key_cols} = mysql_query($query_{$key_cols});
				while($row_{$key_cols} = mysql_fetch_array($result_{$key_cols})){
					if($row_{$key_cols}['id']==$row[$incoming]){
						$data_array[$table] = $row_{$key_cols}[$match];

					// break loops when a match is found

					}

					
				}
			}
			else{
				$data_array[$value_cols] = $row[$value_cols];
			}
		}
		$array[$i] = $data_array;
		$i++;
	}
//	echo '<pre>';
//	print_r($array);
//	echo '</pre>';
	if(is_array($array)){
		return $array;
	}
}

```

I know this problem is very common when working with PHP/MySQL. What is usually your solution?