By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,306 Members | 1,621 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,306 IT Pros & Developers. It's quick & easy.

Multiple Array Selection From Database Instead of In-Page Arrays

P: n/a
Hi,

Have posted before, but will simplify problem here. For original post go to
http://forums.devshed.com/t80025/s.html

I have setup 2 arrays like so in my one page script:

[PHP]
$carers = array(
array('names' => 'Carer 01', 'hours' => 8, 'remaining' => 8),
array('names' => 'Carer 02', 'hours' => 12, 'remaining' => 12),
array('names' => 'Carer 03', 'hours' => 6, 'remaining' => 6),
);

$clients = array(
array('names' => 'Client 01', 'hours' => 2),
array('names' => 'Client 02', 'hours' => 3),
array('names' => 'Client 03', 'hours' => 4),
array('names' => 'Client 04', 'hours' => 1),
array('names' => 'Client 05', 'hours' => 2),
array('names' => 'Client 06', 'hours' => 5),
);
[/PHP]

However, I want to actually retrive this data from a MySQL table. These
arrays are then processed further down the page. The DB looks similar to the
setup here: http://www.monkey-it.co.uk/db_schema.gif

As you can see, I have a DB called 'database' and two tables. Now, how would
I set the page up so that it would retrieve the data from the DB and use it
for processing further down the page like the above arrays would have
originally.

Many thanks,

Janusz

--

************************************************** ***

QOTSA: "Nicotine, Valium, Vicodin, Marijuana, Ecstacy and Alcohol"
Einstein: "Imagination is More Important Than Knowledge"
Jul 16 '05 #1
Share this Question
Share on Google+
12 Replies


P: n/a
On Sat, 30 Aug 2003 11:27:31 +0100, "James" <gr******@dsl.pipex.com> wrote:
However, I want to actually retrive this data from a MySQL table.


http://uk.php.net/manual/en/function...etch-array.php

--
Andy Hassall (an**@andyh.co.uk) icq(5747695) (http://www.andyh.co.uk)
Space: disk usage analysis tool (http://www.andyhsoftware.co.uk/space)
Jul 16 '05 #2

P: n/a
Below is what I have so far. I can retrieve the data I need it, but how do I
sort it like I did in the original?

[PHP]

<TITLE>.: PHP/MySQL Testing :.</TITLE>
<STYLE TYPE="text/css">
h3 {font-family: Verdana, Arial, Helvetica, sans-serif}
font.font {
font-family: Verdana, Arial, Helvetica, sans-serif;
font-size: x-small;
}
</STYLE>

<H3>Connecting to Database and Tables</H3>
<PRE>
<FONT CLASS="font">
<?php
$username = "####t";$password = "####";$hostname = "####";
mysql_connect($hostname, $username, $password) or die("Unable to connect to
MySQL");print "Connected to MySQL <br>";mysql_select_db("database");print
"Connected to Database";
?>
</FONT>

<H3>Fetching Carers Arrays</H3>

<FONT CLASS="font"><?
$result = mysql_query("SELECT name, hours, remaining FROM carers");
while ($row = mysql_fetch_array($result, MYSQL_ASSOC))
{printf ("Name: %s Hours: %s Remaining: %s<BR>", $row["name"],
$row["hours"], $row["remaining"]);}
?>
</FONT>

<H3>Fetching Clients Arrays</H3>

<FONT CLASS="font"><?
$result = mysql_query("SELECT name, hours FROM clients");
while ($row = mysql_fetch_array($result, MYSQL_ASSOC))
{printf ("Name: %s Hours: %s<BR>", $row["name"], $row["hours"]);}
?>
</FONT>

<H3>Original Output</H3>

<FONT CLASS="font">
<?php
$carers = array(
array('name' => 'Carer 01', 'hours' => 10, 'remaining' => 10),
array('name' => 'Carer 02', 'hours' => 12, 'remaining' => 12),
);

$clients = array(
array('name' => 'Client 01', 'hours' => 2),
array('name' => 'Client 02', 'hours' => 3),
array('name' => 'Client 03', 'hours' => 4),
array('name' => 'Client 04', 'hours' => 1),
array('name' => 'Client 05', 'hours' => 2),
array('name' => 'Client 06', 'hours' => 5),
array('name' => 'Client 07', 'hours' => 4),
array('name' => 'Client 08', 'hours' => 2),
array('name' => 'Client 09', 'hours' => 2),
);

$carer_num = 0;

for ($i=0; $i<count($clients); $i++) {

$row = $clients[$i];

// If current carer doesn't have enough hours left, move to the next
while ($carer_num < count($carers) && $row['hours'] >
$carers[$carer_num]['remaining'])
$carer_num++;

// No more carers
if ($carer_num == count($carers)) {
break;
}

// Allocate time
$allocated_hours = min($carers[$carer_num]['remaining'], $row['hours']);
$carers[$carer_num]['clients'][] = array('client' => $row['name'],
'hours' => $allocated_hours);
$carers[$carer_num]['remaining'] -= $allocated_hours;
}

print_r($carers);

?>
</FONT>
<HR ALIGN="LEFT" WIDTH="50%">
<H3>Better Output</H3>
<FONT CLASS="font">
<?php foreach ($carers as $carer)
{ ?>
<BR><STRONG>Name:</STRONG> <BR><UL><LI><?php echo $carer['name']?>
</LI></UL>
<P><STRONG>Hours Allowed:</STRONG><BR><UL><LI><?php echo
$carer['hours']?></LI></UL>
<P><STRONG>Remaining Hours:</STRONG><BR><UL><LI><?php echo
$carer['remaining']?></LI></UL>
<P><STRONG>Clients:</STRONG>
<?php foreach ($carer['clients'] as $client)
{
echo "<UL><LI>{$client['client']} : {$client['hours']} Hours
<br></LI></UL>";
}

echo "<BR><HR align=left WIDTH=25%>";
}
?>
</FONT>
</PRE>

[/PHP
Jul 16 '05 #3

P: n/a
On Sat, 30 Aug 2003 15:35:56 +0100, "James" <gr******@dsl.pipex.com> wrote:
Below is what I have so far. I can retrieve the data I need it, but how do I
sort it like I did in the original? $result = mysql_query("SELECT name, hours, remaining FROM carers");


SELECT name, hours, remaining FROM carers ORDER BY name

--
Andy Hassall (an**@andyh.co.uk) icq(5747695) (http://www.andyh.co.uk)
Space: disk usage analysis tool (http://www.andyhsoftware.co.uk/space)
Jul 16 '05 #4

P: n/a
Cool,

So if I replace this with what is currently there, what would I have to do
next to make the sorting process read from the DB instead of the two static
arrays that are present?

J
Jul 16 '05 #5

P: n/a
???

As a newbie, I haven't the foggiest clue!
Jul 16 '05 #6

P: n/a
On Sat, 30 Aug 2003 17:22:12 +0100, "James" <gr******@dsl.pipex.com> wrote:
???

As a newbie, I haven't the foggiest clue!


I'm not going to write the whole thing for you. You're already fetching from
the database, but all you're doing is printing out the data at the moment.

Add it to an array instead. Read through the link I posted before, for how to
use arrays.

Use the print_r function on the $row variable to see its structure. Think
about what structure you need - it's an array of several of these rows.

--
Andy Hassall (an**@andyh.co.uk) icq(5747695) (http://www.andyh.co.uk)
Space: disk usage analysis tool (http://www.andyhsoftware.co.uk/space)
Jul 16 '05 #7

P: n/a
Fair enough.... I get the following out:

Array
(
[name] => Carer 01
[hours] => 10
[remaining] => 10
)
Array
(
[name] => Carer 02
[hours] => 15
[remaining] => 15
)
Array
(
[name] => Carer 03
[hours] => 9
[remaining] => 9
)
Array
(
[name] => Carer 04
[hours] => 8
[remaining] => 8
)
Array
(
[name] => Carer 05
[hours] => 12
[remaining] => 12
)

any further help would be greatly appreciated, now I have this, how do I put
it into an array?

J
Jul 16 '05 #8

P: n/a
Hi,

Thanks for the reply. Indeed, there are about a dozen factors such as
language issues, level of training etc. This script is one of the most
simple ones that I can do relevant to the project. The project is sctually
just a proof-of-concept that the GRID can provide optimisation for difficult
scheduling problems. In fact the main problem has been outsourced to a
company which are programming the problem in 'C' I believe.

Many thanks for your interest and if anyone could help me with that last
problem I'd be very happy.

J
Jul 16 '05 #9

P: n/a
On Sun, 31 Aug 2003 09:28:03 +0100, "James" <gr******@dsl.pipex.com> wrote:

Hi you seem to be moving on with project now. However a question? Your
method of allocation assumes that all the carers and clients are all in the
same location. Are they? If not you could end up with a carer named Ms
Adams living next door to a client called Mr Wilson driving across town to
care for Mrs Anderson and carer Mr Xavier driving back to Mr Adams to care
for him. You should address this location problem early in your project if
it affects you. Your caring would become more efficient if you had location
information coded into your database structure.


Thanks for the reply. Indeed, there are about a dozen factors such as
language issues, level of training etc. This script is one of the most
simple ones that I can do relevant to the project. The project is sctually
just a proof-of-concept that the GRID can provide optimisation for difficult
scheduling problems. In fact the main problem has been outsourced to a
company which are programming the problem in 'C' I believe.

Many thanks for your interest and if anyone could help me with that last
problem I'd be very happy.


That looks equivalent to the Travelling Salesman problem, which is certainly
NP-complete - except with even more factors. TSP only takes into account
distance. Solving it optimally probably isn't going to be practical unless you
have very few clients (or have made some breakthrough in quantum computing).

Search on Google, there's stacks of research in solving it with heuristics
etc. to get good, if not necessarily optimal, solutions.

http://www.google.com/search?hl=en&i...lling+salesman

--
Andy Hassall (an**@andyh.co.uk) icq(5747695) (http://www.andyh.co.uk)
Space: disk usage analysis tool (http://www.andyhsoftware.co.uk/space)
Jul 16 '05 #10

P: n/a
Cool,

All I want now however is to get rid of the error that shows up at the end

J
Jul 16 '05 #11

P: n/a
On Sun, 31 Aug 2003 14:35:25 +0100, "James" <gr******@dsl.pipex.com> wrote:
All I want now however is to get rid of the error that shows up at the end


What have you tried to do to fix it? If you don't understand what's going on,
then print out each variable involved so you can see what's being processed.

--
Andy Hassall (an**@andyh.co.uk) icq(5747695) (http://www.andyh.co.uk)
Space: disk usage analysis tool (http://www.andyhsoftware.co.uk/space)
Jul 16 '05 #12

P: n/a
I do but still can;'t get to the problem, it just messes up right at the end
when there are no more clients to process

J
Jul 16 '05 #13

This discussion thread is closed

Replies have been disabled for this discussion.