Logic error

182x

Hey guys,

I am trying to put values in the array as follows so they can be used for displaying a graph, the problem I am having is that if in one table there is a user called john instead of counting john as 11 in total it will count 8 from one table and 1 from another and display it this way. Just wondering how to solve this problem.

$get = "SELECT Username, count(*) FROM table1 GROUP BY Username  UNION ALL SELECT Username, count(*) FROM table2 GROUP BY Username "; 
$query = mysql_query($get, $link_id)or die(mysql_error()); 

while($row = mysql_fetch_array($query))
{
$data[] = $row[1];
$leg[] = $row[0];
}

Piranha

As I just posted in this thread, please explain why you use two tables. It may be a design fault in the database.

182x

I am using two tables as they are both storing similar information however there are for different levels of user so one of the tables will have more information and some different types of information and for dealing with this it throughout the program it was easier to use two tables.

Piranha

It is not easier with two tables, it just messes things up (as you have noticed already).

What if you have a user with the high level that is named 182x and a person tries to sign up for the lower level with the same name? It is very hard to solve that problem.

Or what if you have a user on low level that suddenly becomes high level?

Or if the customer decides that there should not exist 2 levels, there should exist 20 levels?

There are a few ways to solve all this. The easiest way is probably to have everything in one table with an additional column that indicates the level. Then you just check that variable to know what level the user is, and thus what information should be shown.

182x

Ah yes, I see what you mean. I will remember to do better design in the future.

For this problem is there a quick way round it as I don't really have time to start from the beginning again for example:

If have two different and unrelated tables and I have done a query to extract infor mation from it and I also have preformed a query to extract information from another table, using PHP and my sql is it possible to perform some form of check so that if the year in table1 equals 2007 and the count is 3 and the year in table 2 is 2007 and the count is 1 so the counts can be added together and im just left with one 2007 value. So the final out put would be 2007 and 5?

	$sql1 = "SELECT year, count(*) FROM table1 GROUP BY year";
		$query1= mysql_query($sql1, $link_id) or die(mysql_error());

$sql2 = "SELECT year, count(*) FROM table2 GROUP BY year";
		$query2= mysql_query($sql2, $link_id) or die(mysql_error());


//don't know what to do here

Piranha

Yes, there are a quick way around it. However I won't post it here since I really don't like sloppy designs and know that in the long run you would benefit from redesigning the database. The problem you have here and now will follow you until you redesign the database, and it will always be a problem. Just redesign it, redo it and do it right this time.

182x

I understand the design is flawed, please could you post the method around it as I really need to get this problem solved and don't have time to do the rest of my code again (it has taken me 3 weeks so far).

I have learned from this mistake and I can assure you this won't happen again, please can you post the other method?

Piranha

To solve the problem (as you say) you should do what I suggest. But it doesn't mean that you have to redo all the code, you only have to fix the queries.

What you want is a quick fix. Unfortunatley there is no such thing. So no, I can't post the other method.

Roger_Ramjet

Further to the one-table design:

in full 3rd normal form, the data would be split into 2 tables

table 1 would contain the columns that are common to all users
table 2 would be an extention of table 1 and contain the additional columns that only some users have.

You would then use a LEFT JOIN to unite the 2 tables and return the full data for all users.

However, because of the several shortcomings that mysql still has with regards to Referential Integrity and Updatable Views, you may just prefer to suffer the rows with empty columns in 1 table.

Piranha

I don't agree with you on this one Roger. Of course it is possible to use 2 tables, if you know databases good it is preferred. But it is not breaking the 3rd normal form to use one table. Consider the example below:

[b]Everyone[/b]             [b]Admin[/b]
id                   id
name                 phone
mail

Phone is only dependant on id, not on anything else. So you can do the following and leave it as null where the person is not an admin:

[b]Everyone[/b]
id
name
mail
user_level
phone

Both phone and user_level is directly dependant on the id. If it was not in 3rd normal form phone would be dependant on user_level, which it is not.

Roger_Ramjet

Well now, Piranha, in your example phone IS dependant on user level: or rather the decision to include and store a phone no. Doubtless all users have phone nos but it is their user level that makes such number significant.

Nothing is dependant on ID which has no meaning in the 'real' world and only exists as a convenience to facilitate the internal workings of the database.