0

I got 2 tables which I want to select via mysql_query in php.

  • table1

columns: specialID, Name, Information, Start, created, byUser, ziparea

  • table2

columns: ID, ziparea, cusID, title, contact, mail

$result = mysql_query("SELECT * FROM table1 JOIN table2 ON table1.ziparea = table2.ziparea WHERE table1.specialID='".$_REQUEST["specialID"]."'");
while($var = mysql_fetch_array($result)) {
}

table1 has exactly 1 entry for each ziparea from 01 to 99. table2 has multiple entries for zipareas.

The problem which seems to occur is that the result seems to be something like:

  • table1-data --- data1 ---- data1 --- data1
  • ziparea -------- 24 -------- 24 -------- 24
  • table2-data --- data1 ---- data2 --- data3

means the data of table1 gets copied. I want the cells to be empty, because if I i.e. ask for table1.Name in php, 

<?php echo utf8_encode($var["Name"]); ?>

it should only give 1 data... now it comes with 3 copies of that one data I need.

How can I solve this problem?

Roy
  • 13
  • 6
  • In this example you dont even need the 2nd table: select name from table1 where specialID =... – Alfons Apr 01 '14 at 16:18

1 Answers1

0

Try adding in a GROUP BY into your SQL Query, such:

$result = mysql_query("SELECT table1.Name FROM table1 JOIN table2 ON table1.ziparea = table2.ziparea WHERE table1.specialID='".$_REQUEST["specialID"]."' GROUP BY table1.Name");
ScottMcGready
  • 1,612
  • 2
  • 24
  • 33
  • select * aint gonna work then, use select table1.name ;) – Alfons Apr 01 '14 at 16:17
  • Now it does't give more than one data from table1, but it also only gives 1 data for table2, where I need all data from the ziparea column – Roy Apr 01 '14 at 16:19
  • Your question said that you're getting 3 copies of the data and that you only need one, a `GROUP BY` statement would solve this. Are you saying that you actually do need all 3 "copies"? – ScottMcGready Apr 01 '14 at 16:21
  • no, lets say for example ziparea is 24 table1 has only 1 row where ziparea is 24 table2 has 4 rows where ziparea is 24.. if I join them, table2 will have 4rows with ziparea 24 and it seems like table1 fills up the 3 rows, where it actually has no data – Roy Apr 01 '14 at 16:24
  • and I need all the data from table1 and all the data from table 2 to start from 0, maybe I run the wrong query, I don't know: I need all data from table1, where specialID = Request(specialID) table1 also gives me the ziparea (for example 24) now I have multiple entries in table2, where ziparea is 24 and I need all of them – Roy Apr 01 '14 at 16:28
  • I think you need to clarify your question a little bit. – ScottMcGready Apr 01 '14 at 16:29
  • [1]: http://i.stack.imgur.com/wgOTO.png I need all the data from table1 on my php-page, but I also need all the information from table2 in the same php-page. Since I'm not to much into SQL I thought about just having 2 queries, which didn't work out. I surfed the internet to find out that JOIN might be the solution for my problem. But now I got the problem that I have multiple entries from table1, where actually only 1 entry should exist. And with the Alternative of GROUP BY, it seems like I delete every entry of table2 but 1. – Roy Apr 01 '14 at 16:38
  • isn't there the possibility of using it like `` so it will always only go for the first entry? – Roy Apr 01 '14 at 16:41
  • The SQL queries we're running here are not editing the data within the database, merely manipulating it for display, there's no extra entries being added or deleted. Your image is almost correct, apart from the `tenderID` column, which will be 1 all the way down in your case. Therefore it is almost what you are after, the only difference is the data in the 1st 6 columns are repeated downward. – ScottMcGready Apr 01 '14 at 16:43
  • The query I gave in the answer will do what you need (only thing to bear in mind is that the tenderID will be the same all the way down, your 3rd image is actually incorrect). From there if you want it to display as blank cells, perhaps run a check in PHP to see if the current tenderID is the same as the last row, if it is don't print anything. – ScottMcGready Apr 01 '14 at 16:52