Array Noob | Multiple Dynamic Arrays

[blasterstudios]

I am trying to setup a database that stores drop-down box values in separate tables. When the option value is selected, i'd like the id of that row to be stored in a new table, rather than the text. That way i can manage the entire system rather than each actual value. For example:

clientid 1 = McDonalds
Requestorid 3 = John

Job #1:
Client = 1
Description = build a new one
Requested by = 3

So instead of each field echoing the actual id, i would like it to reference the corresponding client in the client table. As well as the requestor. I needs to put "John" in place of the 3. How can I setup multiple arrays to do this. In the end, i'll have a bout 6 or 7 total.

Thanks.

 

[sleipnir214]

This is probably harder than you're thinking. Where are you going to store the values of those arrays between runs of the program?

Have you considered actually using a database, such as MySQL?

Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[blasterstudios]

I'm sorry. I didn't specify. These values are all stored in a MySQL Database in separate tables (i.e. clients = tblClients, jobs = tblJobs, etc.)

 

[KingCrab]

So a drop down box would be

<option name="Client" value="1">McDonalds</option>

Maybe I dont understand what your trying to do.

 

[blasterstudios]

oops. I meant to explain further. The problem comes not when I update the records, but when I am echoing the records onto a list of jobs page. the values for the job are like:

Jobid: 1
Client: 1
Requestor: 3
Status: 2

But these values need to reference things in other tables. like:

Status List:
1 = Open
2 = Pending
3 = Closed

Client List:
1 = McDonalds
2 = Sears
3 = Sony

So when i echo each job, i would like for it to cross reference each table with the value for that field. The way I understand it works is that you would create an array for each table, then whenever it looks for a "clientid" instead of just echoing "1" you would reference id 1 in the clients table, and then i could echo the "clientname". See what I mean?

 

[blasterstudios]

Sorry, i've done database programming in Visual Basic but its slightly harder in PHP when its all code. In VB, I can create relationships between tables so if i pull a value from 1 table that corresponds with a relationship to another table, it automatically picks the row that the id references and pulls the fields from there. I hope this is clear, it's hard for me to put into words i guess.

 

[vacunita]

I think i understand what you want, but this is done at a query level, not at the PHP level, When you create your queries you tell it what info you want.

For example imagine you have the following two tables.

Table 1
Clients

Client_ID=1
Name=John
Address=....


Table 2
Invoice

Invoice_I=254
client_ID=1
ammount=$500
...
Suppose you want invoice 254.

you query would look like:

Select *from invoice where invoice_id=254

If you want to get the the invoice data and the client the invoice belongs to with all its info. for that invoice, the query would look something like:

Select *from clients,invoice Where invoice.invoice_id=254 and clients.client_id=invoice.client_ID;

This will output:
invoice_id client_id ammount.. name address ...
254 1 500 John someaddress.




Is this what you need??

----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[blasterstudios]

well, i think i'm pretty clear on how to do that. The issue is pulling multiple "invoices". Here's what i'm trying to do:

List of invoices:
invoice 1 | client | date | etc. (click to view/edit)
invoice 2 | client | date | etc. (click to view/edit)

I'm trying to display all of that information at once, not just one specific job, but for example ALL OPEN JOBS.

I think the best way may just be to update each record with the actual client name/user etc. I was hoping that if a change was made to the client, it would change it everywhere rather than having to change each record individually.

Example:
Someone sets up client "McDonelds"
Nobody notices that "McDonalds" is misspelled until 200 jobs have been created for that client.

My way would only require 1 change. The alternate way would require 201 changes.

 

[hos2]

hmmm I think you explain it harder then it is

you just want a select field of you're clients that can be added to a job. when the record is inserted or updated you do'nt want mcdonald to be entered in the job but the id of the client.

you query all you're clients like ( I wrote it in a statement like below, saves a lot of typing when you have multiple select options. Now I copy paste this code and change the query and the var name in the end. further on I can put the variable in the print code in the form so I can still easily read my form without having all kinds of loops running through it

$query="SELECT * from client order by name";
$q_result=mysql_query($query,$conn);
$templist='';
$templist = "<select name=\"client\"><option value=\"\">Empty";
while ($ary_stage = mysql_fetch_assoc($q_result)) { //one status
while (list($key,$val) = each($ary_stage)) { $$key = $val; }

$templist .= "<option value='" . $client_ID . "'";
if ($client==$client_ID) {$templist .= " SELECTED ";}
$templist .= ">" . $name;
} //end one stage
$templist .= "</select>";
$clientlist=$templist;


the var $clienlist you can put in you're form and then you have the selectlist. if someone selects mcdonalds the id of the client is stored in the field client. so if you process you're form then the ID is transferred and not the name

 

[vacunita]

If you want all invoices, nstead of specifying an invoice number you do:

Select *from invoices, clients where invoices.id_client=clients.id_client;

This is going to pull every invoice, and then pull the info for the clients that mathces each one.


so you would get:

invoice 1 client 234 John addres...
invoice 2 client 532 Peter adress...
and so on and so forth.


You Client Table would contain all the information for clients, and that would be pulled anywhere you needed via the client_id in the invoice table. SO you would only need to fix the client Mcdonalds, and automatically all the invoices related to mcdonalds would get updated. Ths is really simple relational DB construction.


You have one table with each client's info in a row.
and then you relate it to the invoices so if client mcdonalds has 3 invoices, you only have to fix the name once in the table clients, not everywhere since its there where it it pulled from. You dont actually have it everywhere. You just have a sort of pointer to it. in eahc fo the invoices rows.



----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[blasterstudios]

ok, this makes sense, but how would i do this for:
tblClients
tblPriorities
tblJobs
tblStatus
tblUsers

Each job has a client, status, priority, user, etc. Everything is referenced by their ids. So for instance if i wanted to add or change the values for our Priorities or Statuses, i could do so. I want the whole system to be alterable.

Is this possible?

 

[blasterstudios]

ok, what i was talking about arrays before. I know how to setup an array with entered values. This is sort of what i'm doing.

$userlevels = array ("Standard Admin", "Super User");

Then when the id of each array is referenced, the word appears rather than the number.

if($row_admins['level'] > 0 and $row_admins['level'] < 3) {echo $userlevels[$row_admins['level'] - 1];}

Is this not what i'm trying to do the same as this, but using arrays created by what's in the database, rather than just values typed in quotes?

 

[hos2]

hela blasterstudios have you actually investigated some of the solutions provided above ??? For instance you just have to copy my code for every field you have and you have what you ask for. you can then update or insert that info in you're database. and why create arrays if you can dynamicaly get it from you're database . Perhaps you should investigate MYSQL a bit before continuing with arrays

 

[blasterstudios]

ok, i don't think an array is exactly what i need. i was actually just looking at table relationships for MySQL because that's the way I used to do it with Access. I guess what i really need is help setting up the table relationships and properly echoing the values to do what i want.

 

[hos2]

you don't need to build relationships between the tables here. you just use the select function I provided for each table and the relationship is automaticaly defined. In big systems like SAP you also don't define relationships between tables but define everything through you're code

 

[blasterstudios]

i don't think you understand....

I am not having a problem setting up the selection boxes. I know and understand how to do that. I think I may have said that once or twice even.

The problem i'm having is pulling values from 1 table when referenced by another table.

 

[hos2]

how do you mean??

you have a table job

jobid
clientid

you have a table client
clientid
clientname

if you want to have clientid in table job you just loop though table client like I have descriped. you see the clienname but the value is clientid. when you update or insert you're info then the clientid is saved in table job

update job set clientid='$POST['clientid] where jobid='$POST['jobid']

 

[blasterstudios]

Ok, maybe you're helping, but i just don't see it. I see where you have shown me how to setup the combo box to setup the values as the ids and the name as the actual name. I didn't need that.

I see where you are giving me an update query which is farthest from what i need.

I think what i need is in some sort of extremely complicated query that I don't know how to execute. All i'm doing is listing these jobs to a page. I need no assistance in actually putting them into the database. I know how to do that. They're already in the database. Its a matter of getting them out in a way that a normal person can read it. For example, the value in the database for client is 1, and the value for rep is 3. Well, that wouldn't make any sense to a normal person. I need the client id of 1 to lookup in the client table and then come back with the name that corresponds with its id. i need it to do the same for the rep, status, priority, and job type.

 

[hos2]

it are all seperate qeuries for each table 1 if you want to have select lists in you're form. if you just need to output the information on the screen you can join the tables

select * from job,client,status where job.clientid=client.clientid and job.statusid=status.statusid etc

you can then put client.name on the screen

if you look at an example that I'm working on then you see it with the select lists which are (except from the date) pulled from the individual tables and stored with their ID

http://thehospages.com/concertagenda/agendaform.php?id=agendaadd

on the output you see the complete names through a join query as described above

http://thehospages.com/concertagenda/agendalist.php?id=agendalist

 

[blasterstudios]

ok. i figured out what i need to ask. i'm going to start a new thread because it's unrelated. I thought it was an array i needed help with, but is the structure of the query.

thanks for trying to help.