[RESOLVED] How to do it properly?

__berfuzz

I use php/my_sql to get info from a sql-table. One of the solutions I have now is somewhat complicated and the idea is to rebuild it. I have the folloing sql-table:

I would like to get it out in a html site, something like this:
category1
item1 | 2 st
item2 | 2 st
item3 | 1 st

category2
item1 | 2 st
item2 | 1 st
item4 | 2 st

How should I solve this in the best way? The pressent code is arrays back and forth. From two dinesional back to several singel arrays. My idea for the new solution is as follows:
1. sql query the two columns.
2. Se if category exists.
3. Make an new array if a new category apears name it 'category1' and so on.
4. explode 'item'
5. put in exploded array in right array/category.
6.1. make echo of the first array-name. (Is their such function..?)
6.2. make echo-loop of key() & array_count_values()

To me some of the steps seems to be weak and I'm not sure of how to do them. How should i proceed?

Piranha

Basically you need to rethink your database design. Read up on normalization to get to know why and what to do. Basically you could do all the steps with one query once you have done it, the query would look something like this:

SELECT category.category, item.item, count(item.id)
FROM category
LEFT JOIN item ON category.id = item.categoryid
ORDER BY category.category, item.item

__berfuzz

Okej.

I'm not sure if I understand how to do it. Not even sure I'm promitted to change the structure... Well well. It seems better.

The data coming from the database is often around 100 rows..? Two tables - one query?

//sql-structure: id, user, phone, client, item, check, date
id - auto
user - the one who registered
phone - the costumers is registered by phonenumber
client - the company
product - the purchased product, sometimes need explode().
check - controll
date - date

Should I make two tables:

table_customer:
user - the one who made registered
phone - the costumers is registered by phonenumber
item - info from table item
check - controll
date - date

table_item: // client is given by item.
client - the company
item - the purchased product, sometime explode().

tomhath

I would make it like this. There can be several table_item rows for one table_customer row, no need to use explode.

table_customer:
table_customer_id - auto
user - the one who made registered
phone - the costumers is registered by phonenumber
client - the company
icheck - controll

table_item:

table_customer_id - foreign key to table_customer
item - the purchased product
date - date of purchase (?)

__berfuzz

I read a bit about getting the info out. Thanx pirahna! Is there an easy way to get the info in as well? One (insert) query - two tables?

edit: bad spelling.

Piranha

Nope, not to my knowledge. You basically have to insert the row in one table, get the ID and use that when inserting in the second table.

__berfuzz

Hmm. I guess you're right, me trying that was all in vain.

Then it would be 3 querys, or?
Code as the steps below:
1. insert one row in result.
2. get the id key given to that row.
3. insert rows in product table with id key from row in result.
1 & 2 ok, but how do I do nr 3 the best way?

Just to give an idea of how client and product comes from a form:

$product = explode(' - ', $_POST['product']);   //$_POST['product'] needs to be exploded to get client and product
$client = $product[0];
$product[0] = $product[1];  //redefining $product[] to be inserted in product_table.
unset($product[1]); // just in case there is no 'extra' to the product
if(isset($_POST['port'])){   //$_POST['port'] is set by a checkbox in the <form>
   $product[1] = $port;
}
if(isset($_POST['music'])){   //$_POST['music'] same as $_POST['port']
   $product[2] = $music;
}

If theres no better way. How do I insert the array?

foreach ($product as $key=>$a){
   $query = "INSERT INTO product VALUES ('$id','$a')";
   if (!mysql_query($query,$conn))
   {
      die('Error: ' . mysql_error());
   }
}

Piranha

It is 3 queries to the database, but the second one should be the function called mysql_last_id (or something simular) in PHP. That way you don't risk that someone else have inserted a row, thus getting the wrong id.

If you don't have to know the id of the inserted rows you should use this synax in the insert (you should always use it, but especially now).

insert into table (name, address)
values ('kalle', 'stockholm'),
('pelle', 'gothenburg'),
('olle', 'malmoe')

That way you can insert many rows into the same table with just one query.

__berfuzz

Du glömde Uppsala

Piranha

Hehe 🙂

__berfuzz

Actually, I read about normalization and how to get info out of normalized tables etc. Seems to be dandy. However, my old solution works like a charm, with one or two extra rows of code.
Thanx!

Piranha

You are wrong. Normalization is there to make everything easier, not to make it harder. It might be that you have found a workaround this time. But you will get in trouble again when changing the database/script. The only way to solve the problems permanently is to normalize.

It is clear that you don't understand enough about databases and database design to understand this yet. It is ok, we have all been there. What you should do about it is to ask (of course), and then use what is suggested. I would not put my time to read questions and write answers to mess it up for people, and most persons that answer is trying to help. Normally following the answers will make your life easier. Another benefit of following advice is that people will answer your next question as well, people that don't follow advice won't get their questions answered. Why? Because I don't want to spend my time helping people that don't want help. I have better things to do than that.

Oh, and another thing. I suggest that you don't ask how to do it properly if you don't want to know. Ask for a quick and dirty fix next time, then you might get a solution to something that will solve the current problem but not work in the long run.

__berfuzz

Wow. You seem to jump to conclusions you shouldn't. Not good for you mood! "Actually, I read about normalization and how to get info out of normalized tables etc. Seems to be dandy."

It's clear that coding is a hobbie for you. For me it's part of my work. A fraction of my working time is spent fixig the site. It's very sweet of you to help me. Somewhere down the line I might find time to sort up the database. I don't know if thats something you can understand but the folks paying my salary doesn't see normalization as such a big deal, as you seem to do.

I never asked for code, I never said that I will use the tips I would receive. You can't honestly say that you believe that evry anser given here is going to be used just becouse it happans to be a good one.

Next time I post with a head line like, how to do it properly, don't read something thats not there. Cheers!

Piranha

Well, you are mistaken. Not about me jumping to conclusions or my mood, but about it being a hobby for me. I am a software developer, and work with these kind of things daily. About 90% of my work is developing, that includes databases. But the difference is that databases is part of my work, it doesn't seem to be part of your.

The reason I try to push you to normalize is that it will make your life easier. For a new system there is no reason not to normalize. To rebuild an existing system is harder, but normally you will gain by doing it.

I don't believe that everyone follow good advice, I know that lots of people don't. When people ask "and how do I do that" when I suggest something I can just ignore them. But you seemed to be willing to put in the extra bit of work, both to read up and follow advice. That is why I think you were worth the extra bit of time, and that is why I think it is sad that you decided not to use it.

I think we are a bit off topic here, I don't think that I will post more in this thread.

__berfuzz

Well, it might be out of topic, but stil, it's kind of a pressing issue in thies comunities. When I write or read posts I allways like to keep controll. Over the code I use that is. Problem is I can't seem to find time to sit down just playing around. I want to have a clear idea of what it is I'm going to do before I write the code. Seems to me that I save a lot of time doing it that way. The issues in this thread has been very helpful for me. Given me a new way to work with databases.

Ever so often I se posts where someone has taken a script from some scritp archive looking for help to get it work properly. Is it wrong or right? Don't know, but as you imply, it's not encouraging to help a poster that won't put in any effort. In my case the effort wasn't coding, but reading. ;-) And it will be in the back of my mind til the time comes. Normalization.