PHP & MySql if....

[dak888]

Hello all,

Here is the set-up.
MySql table that has 6 columns. Vend1, Vend2, Vend3 and odate1, odate2, odate3.

I want to update or insert a new record on submit. If can write an if statement in PHP no problem that will either update or insert the new record based on if one exists or not.

The problem:
If updating the record, I need it to check whether vend1,2,3 and odate1,2,3 has a value. If it does, then write to the next available column.

Ex: vend1 and odate1 have values but vend2 and odate2 do not. So it would go, vend1 and odate1 are not empty, write the information to vend2 and odate 2 which are empty.

Hopefully that makes sense. I just can't wrap my head around how to do it!

Thank you for any direction.

DaK

[LLFitness_Derek]

This post was a little confusing but after reading it several times I believe I can offer a solution. First I will repeat what you stated but a little clearer to make sure I am getting the picture.

Database [VEND_DATE]
Fields [Vend1, Vend2, Vend3, odate1, odate2, odate3]

You are updating VEND_DATE with the value $_GET['new_vend'] and $_GET['new_date'].

First I need to say you can not update a record based on this database structure. This table does not have a primary key used as an identifier. How do you identify the data that you are updating. How can you tell between data you have already entered and the data you are updating.

Would this database structure fit your purpose better:
Database [VEND_DATE]
Fields [Vend_id, Vend_name, Vend_date]

From here you can run your update query:
UPDATE VEND_DATE SET Vend_date='whatever_date' WHERE Vend_id='whatever_id'

Or a SELECT query such as:
SELECT Vend_date FROM VEND_DATE WHERE Vend_id='whatever_id'

Am I getting the picture? If not can you provide a little more information on what you are trying to do. Give a real world example to make things clearer.

[dak888]

Hello LLFitness_Derek. Perhaps that's why I haven't got a lot of responses, lol!

The table actually has 22 columns with a column called ord_num (order number) that serves as it's unique identifier. The operation I'm trying to work on only updates the 6 columns that I mentioned in my first post.

Sorry for any confusion, still very new to this...

This table stores some extra information about a customers order.
vend1, 2 and 3 are for names of vendors and odate1, 2, 3 are dates in which orders are placed. Vendors and order dates are related to each other in that each vendor entry should have a corresponding order date. So if there is an entry in the vendor column (say vend1) then there should be a corresponding date in the order date (odate1). This way we know that the product was order from X vendor on X date.

What I'm trying to do is when one of our reps hits a submit button to send the order to the vendor, the vendor name and order date are updated in the table automatically.

Now I've made room for 3 vendors and corresponding order dates (which "should" be more than enough) in the table. However, on rare occasion, we do send orders to more than one vendor so I want to make sure we don't over write any information that is already in the table.

So that's where my original post comes from. I don't want to over write vendor and order date information if there is already some in the table. I just can't figure out how to do that.

Hopefully that makes a bit more sense.

Thanks,

DaK

[LLFitness_Derek]

Dak

I'm pretty sure I get most of the picture, unfortunately you will need to alter your database structure to get the desired results. First you need to familiarize with the database syntax: Database, Table, Field, records, etc... Makes it easier for you to understand exactly what I am suggesting.

About the structure... You should never create table fields by "Guessing" how many vendors and orders you will have.

It sounds like you need two tables:
1. Vendors
2. Orders

The table Vendors should have these type of fields(columns):
vendor_id(auto-increment) Primary Key
vendor_name
vendor_description
etc...

The table Orders should have these types of fields(columns):
order_id(auto-increment) Primary Key
vendor_id (reference to the vendor_id in table Vendors)
order_date
order_time
order_total
etc...

When a vendor is logged in he/she should have a vendor id. When they hit the submit button a query should run in your database:
INSERT INTO `Orders` VALUES(' ',vendor_id,date,time,total, etc)

When you want to view the data you would run queries such as:
SELECT order_id, order_date, etc... FROM `Orders` WHERE vendor_id="any_id" ORDER BY time DESC LIMIT 3

This would return the newest three orders from the vendor you specify. LIMIT controls the MAX number of results.

Is this more along the lines of what you are looking to do?

Hope im getting warmer.

Derek

[John Redfield]

I agree with Derek, you will run into trouble later on if you dont normalize your database atleast to 3NF.

If you are not familair with relational database design there are good tutoitals online, here is one: Database Normalization Basics for Developers

John