Create 2dimensional array from database (and convert to js)

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
mxbrainwrong
Forum Newbie
Posts: 7
Joined: Wed Feb 15, 2012 6:20 am

Create 2dimensional array from database (and convert to js)

Post by mxbrainwrong »

Hi folks,
I'm trying to create a two dimensional javascript array from the contents of an sql database. I need it to take the form:
array[row 0][field 0];
array[row 0][field 1];
array[row 0][field 2];
array[row 1][field 0];
array[row 1][field 1];
etc etc, flexible to accommodate however many rows and fields there are in the database table.

I've sussed out how to convert a php array to javascript (easy using json_encode($array)). The problem I'm having is rendering it in the requitred format. I'm pretty new to all this so its' highly likely that the answer will be obvious and simple but I've come up against a brick wall and would appreciate some help.

The relevant code I've written so far is as follows:

Code: Select all

$result = mysql_query("SELECT * FROM exampleTable ORDER BY ID") or die('No table found');
while ($row = mysql_fetch_array($result)){
		$array[] = $row;
		}

echo 'var array = ' .json_encode($array).';';
Which does, admittedly, return the contents of the database table but not in a format that is useful or usable for what I want to do. I was thinking of something along the lines of:

Code: Select all

$result = mysql_query("SELECT * FROM exampleTable ORDER BY ID") or die('No table found');
$array = array();
	
	for($i=0; $i<=4;$i++){
		for($ii=0; $ii<=4;$ii++){
			$array[] = array();
				$array[$i][$ii] = array($row[$i]$col[$ii]);
		}
	}

	
	echo 'var array = ' .json_encode($array).';';
... but this is leading to all sorts of problems. In short I'm having problems with the syntax around the for loop for generating the array and after many hours trawling the net for clues am no closer to getting it to work. Like I say, any help would be much appreciated.

Many thanks
Stef
G l a z z
Forum Newbie
Posts: 24
Joined: Sun Feb 12, 2012 10:33 pm

Re: Create 2dimensional array from database (and convert to

Post by G l a z z »

Try something like this:

Code: Select all

$array = array();
$result = mysql_query("SELECT * FROM table ORDER BY ID") or die('No table found');
while($row = mysql_fetch_array($result)):
	foreach(mysql_fetch_array($result, MYSQL_ASSOC) as $key => $value):
		$array['row_'.$row['id']][$key] = $value;
	endforeach;
endwhile;
Not the best code, but it does the job, i think.
mxbrainwrong
Forum Newbie
Posts: 7
Joined: Wed Feb 15, 2012 6:20 am

Re: Create 2dimensional array from database (and convert to

Post by mxbrainwrong »

Thanks Glazz,
Had something like that sort of working before. The idea though is to step away from the whole key/value thing as I find it harder to manipulate in javascript (my weakness there). However, gonna have a tinker with this and see if I can boost my javascript horizons enough to utilise it. Thanks for replying.
Stef
mxbrainwrong
Forum Newbie
Posts: 7
Joined: Wed Feb 15, 2012 6:20 am

Re: Create 2dimensional array from database (and convert to

Post by mxbrainwrong »

Actually, my bad... should have written that I wanted to create it in this format (is it even possible in php?):

array[row 0][field 0, field 1, field 2, field 3, field 4];
array[row 1][field 0, field 1, field 2, field 3, field 4];
array[row 2][field 0, field 1, field 2, field 3, field 4];

The static (ie non database driven) array I was originally working with looked like this:

var galleries = new Array();
// Order: id, size, date (yyyymmdd), name, description, thumb image, left px, top px, z-index
galleriesOriginal[0] = [0, 12,"20111012","Cats","A gallery of beautiful cat pictures","images/catThumb.jpg",480, 230, 42];
galleriesOriginal[1] = [1, 10,"19990703","Dogs","A gallery of beautiful dog pictures","images/dogThumb",580, 230, 100];
galleriesOriginal[2] = [2, 7,"19970401","Marmolsets","A gallery of beautiful marmoset pictures","images/marmosetThumb.jpg", 580, 330, 100];
galleriesOriginal[3] = [3, 1,"20080610","Anvils","A gallery of beautiful anvil pictures","images/anvilThumb.jpg", 380, 430, 100];
galleriesOriginal[4] = [4, 13,"20101212","Xylophones","A gallery of beautiful xylophone pictures","images/xylophoneThumb.jpg", 680, 430, 100];
etc
So basically, trying to reproduce that format but generated from the database via php...

Any ideas?
Thanks again
Stef
G l a z z
Forum Newbie
Posts: 24
Joined: Sun Feb 12, 2012 10:33 pm

Re: Create 2dimensional array from database (and convert to

Post by G l a z z »

How are you accessing the array?

galleriesOriginal[0][2] - something like this ?
mxbrainwrong
Forum Newbie
Posts: 7
Joined: Wed Feb 15, 2012 6:20 am

Re: Create 2dimensional array from database (and convert to

Post by mxbrainwrong »

Thats sort of what I'm trying to work out :-(
mxbrainwrong
Forum Newbie
Posts: 7
Joined: Wed Feb 15, 2012 6:20 am

Re: Create 2dimensional array from database (and convert to

Post by mxbrainwrong »

ah, sorry, misunderstood...
Jquery is:

function display (array) {

for (i=0, max = array.length; i<max; i++){
$('#container').append('<div id="'+array[0]+'" style="position: absolute; left:'+array[7]+'px; top: '+array[8]+'px;"><a href='+array[6]+'><img id="nav" src='+array[5]+' width=75 height=75></a></div>');
}
}
mxbrainwrong
Forum Newbie
Posts: 7
Joined: Wed Feb 15, 2012 6:20 am

Re: Create 2dimensional array from database (and convert to

Post by mxbrainwrong »

Am now trying something along these lines but not 100% sure how to do it:

for ($i=0; $i<= $num_rows; $i++){
for ($ii=0; $ii<= $num_fields; $ii++){
echo("$row[$i]<br />");
}
}
G l a z z
Forum Newbie
Posts: 24
Joined: Sun Feb 12, 2012 10:33 pm

Re: Create 2dimensional array from database (and convert to

Post by G l a z z »

Well i don't know if this is going to do the trick for what you need

Code: Select all

$array = array();
$i = 0;
$result = mysql_query("SELECT * FROM email_templates ORDER BY ID") or die('No table found');
while($row = mysql_fetch_array($result, MYSQL_NUM)):
	foreach($row as $key => $value):
		$array[$i][$key] = $value;
	endforeach;
	$i++;
endwhile;
mxbrainwrong
Forum Newbie
Posts: 7
Joined: Wed Feb 15, 2012 6:20 am

Re: Create 2dimensional array from database (and convert to

Post by mxbrainwrong »

That worked beautifully. Many thanks for your help.
G l a z z
Forum Newbie
Posts: 24
Joined: Sun Feb 12, 2012 10:33 pm

Re: Create 2dimensional array from database (and convert to

Post by G l a z z »

No problem =)
Post Reply