Multiple Array Selection From Database Instead of In-Page Arrays

[James]

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: "Imaginatio n is More Important Than Knowledge"

[Andy Hassall]

Re: Multiple Array Selection From Database Instead of In-Page Arrays

On Sat, 30 Aug 2003 11:27:31 +0100, "James" <graduate@dsl.p ipex.com> wrote:
[color=blue]
>However, I want to actually retrive this data from a MySQL table.[/color]

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

--
Andy Hassall (andy@andyh.co. uk) icq(5747695) (http://www.andyh.co.uk)
Space: disk usage analysis tool (http://www.andyhsoftware.co.uk/space)

[James]

Re: Multiple Array Selection From Database Instead of In-Page Arrays

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>Connectin g to Database and Tables</H3>
<PRE>
<FONT CLASS="font">
<?php
$username = "####t";$passwo rd = "####";$hostnam e = "####";
mysql_connect($ hostname, $username, $password) or die("Unable to connect to
MySQL");print "Connected to MySQL <br>";mysql_sel ect_db("databas e");print
"Connected to Database";
?>
</FONT>

<H3>Fetching Carers Arrays</H3>

<FONT CLASS="font"><?
$result = mysql_query("SE LECT name, hours, remaining FROM carers");
while ($row = mysql_fetch_arr ay($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("SE LECT name, hours FROM clients");
while ($row = mysql_fetch_arr ay($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($clien ts); $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_hour s = min($carers[$carer_num]['remaining'], $row['hours']);
$carers[$carer_num]['clients'][] = array('client' => $row['name'],
'hours' => $allocated_hour s);
$carers[$carer_num]['remaining'] -= $allocated_hour s;
}

print_r($carers );

?>
</FONT>
<HR ALIGN="LEFT" WIDTH="50%">
<H3>Better Output</H3>
<FONT CLASS="font">
<?php foreach ($carers as $carer)
{ ?>
<BR><STRONG>Nam e:</STRONG> <BR><UL><LI><?p hp echo $carer['name']?>
</LI></UL>
<P><STRONG>Hour s Allowed:</STRONG><BR><UL> <LI><?php echo
$carer['hours']?></LI></UL>
<P><STRONG>Rema ining Hours:</STRONG><BR><UL> <LI><?php echo
$carer['remaining']?></LI></UL>
<P><STRONG>Clie nts:</STRONG>
<?php foreach ($carer['clients'] as $client)
{
echo "<UL><LI>{$clie nt['client']} : {$client['hours']} Hours
<br></LI></UL>";
}

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

[/PHP

[Andy Hassall]

Re: Multiple Array Selection From Database Instead of In-Page Arrays

On Sat, 30 Aug 2003 15:35:56 +0100, "James" <graduate@dsl.p ipex.com> wrote:
[color=blue]
>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?[/color]
[color=blue]
>$result = mysql_query("SE LECT name, hours, remaining FROM carers");[/color]

SELECT name, hours, remaining FROM carers ORDER BY name

--
Andy Hassall (andy@andyh.co. uk) icq(5747695) (http://www.andyh.co.uk)
Space: disk usage analysis tool (http://www.andyhsoftware.co.uk/space)

[James]

Re: Multiple Array Selection From Database Instead of In-Page Arrays

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

[James]

Re: Multiple Array Selection From Database Instead of In-Page Arrays

???

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

[Andy Hassall]

Re: Multiple Array Selection From Database Instead of In-Page Arrays

On Sat, 30 Aug 2003 17:22:12 +0100, "James" <graduate@dsl.p ipex.com> wrote:
[color=blue]
>???
>
>As a newbie, I haven't the foggiest clue![/color]

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 (andy@andyh.co. uk) icq(5747695) (http://www.andyh.co.uk)
Space: disk usage analysis tool (http://www.andyhsoftware.co.uk/space)

[James]

Re: Multiple Array Selection From Database Instead of In-Page Arrays

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

[James]

Re: Multiple Array Selection From Database Instead of In-Page Arrays

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

[Andy Hassall]

Re: Multiple Array Selection From Database Instead of In-Page Arrays

On Sun, 31 Aug 2003 09:28:03 +0100, "James" <graduate@dsl.p ipex.com> wrote:

[color=blue]
>Hi you seem to be moving on with project now. However a question? Your[color=green]
>>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.[/color]
>
>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.[/color]

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&ie=UTF-8&oe=UTF-8&q=travelling+salesman

--
Andy Hassall (andy@andyh.co. uk) icq(5747695) (http://www.andyh.co.uk)
Space: disk usage analysis tool (http://www.andyhsoftware.co.uk/space)

[James]

Re: Multiple Array Selection From Database Instead of In-Page Arrays

Cool,

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

J

[Andy Hassall]

Re: Multiple Array Selection From Database Instead of In-Page Arrays

On Sun, 31 Aug 2003 14:35:25 +0100, "James" <graduate@dsl.p ipex.com> wrote:
[color=blue]
>All I want now however is to get rid of the error that shows up at the end[/color]

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 (andy@andyh.co. uk) icq(5747695) (http://www.andyh.co.uk)
Space: disk usage analysis tool (http://www.andyhsoftware.co.uk/space)

[James]

Re: Multiple Array Selection From Database Instead of In-Page Arrays

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