14 Replies - 4099 Views - Last Post: 28 November 2011 - 07:53 AM Rate Topic: -----

#1 localhost  Icon User is offline

  • D.I.C Head

Reputation: 5
  • View blog
  • Posts: 130
  • Joined: 13-December 10

PHP mysql multiple tables order by date

Posted 26 November 2011 - 10:50 AM

Hello,

I've searched a lot on the internet and I couldn't find my answer.

I got 2 tables: table1 & table2 with both columns: date & number.
Now I want to echo the column number from both tables ordered on the date of both tables.
I really don't have a clue how to do this..

Thanks!
Is This A Good Question/Topic? 0
  • +

Replies To: PHP mysql multiple tables order by date

#2 Martyr2  Icon User is offline

  • Programming Theoretician
  • member icon

Reputation: 4333
  • View blog
  • Posts: 12,128
  • Joined: 18-April 07

Re: PHP mysql multiple tables order by date

Posted 26 November 2011 - 11:03 AM

One way you could do this is by querying one table, collect the results and put them in an array, query the other table and add them to the same array and then sort the array.

$sql = "select * from table1";

$result = mysql_query($sql);

if ($result) {
   while ($row = mysql_fetch_array($result)) {
      $myarray[] = $row;
   }
}

// Now do the same with table2.
// myarray will then be an array of arrays which you can do whatever you want with.
$sql = "select * from table2";

$result = mysql_query($sql);

if ($result) {
   while ($row = mysql_fetch_array($result)) {
      $myarray[] = $row;
   }
}




Now if the tables share a field that you can link them on, you can build a query which links the two tables and asks for all the columns from both tables, using an order by clause in the query to sort.

$sql = "select table1.*, table2.* from table1, table2 where table1.somefield = table2.somefield order by table1.datefield";



Hopefully that example gives you the gist of what I am talking about here. :)
Was This Post Helpful? 2
  • +
  • -

#3 localhost  Icon User is offline

  • D.I.C Head

Reputation: 5
  • View blog
  • Posts: 130
  • Joined: 13-December 10

Re: PHP mysql multiple tables order by date

Posted 26 November 2011 - 11:10 AM

Thank you for the fast reply!

I tried something but it doesn't work at all..

My code:
	$result = mysql_query("select ledenwachtwoordvergeten.*, bedrijvenwachtwoordvergeten.* from ledenwachtwoordvergeten, bedrijvenwachtwoordvergeten order by ledenwachtwoordvergeten.geplaatst, bedrijvenwachtwoordvergeten.geplaatst");
	while($row = mysql_fetch_array($result)){
	echo $row['nummer'];
	}


Now I only get the results from bedrijvenwachtwoordvergeten and double!
I don't know why it doesn't work....
Was This Post Helpful? 0
  • +
  • -

#4 Martyr2  Icon User is offline

  • Programming Theoretician
  • member icon

Reputation: 4333
  • View blog
  • Posts: 12,128
  • Joined: 18-April 07

Re: PHP mysql multiple tables order by date

Posted 26 November 2011 - 11:45 AM

You haven't done an inner join on the tables. Notice my example has table1.somefield = table2.somefield in there where clause. Since you don't do this, you are getting what is called a "Cartesian Product" (aka cross-join) where each record in one table is paired up all records of the second table.

Look up the term "Joins" when it comes to SQL queries. :)

This post has been edited by Martyr2: 26 November 2011 - 11:46 AM

Was This Post Helpful? 2
  • +
  • -

#5 localhost  Icon User is offline

  • D.I.C Head

Reputation: 5
  • View blog
  • Posts: 130
  • Joined: 13-December 10

Re: PHP mysql multiple tables order by date

Posted 26 November 2011 - 12:04 PM

I already looked to use join but I can't figure it out.
My problem is.
The numbers in table 1 and 2 are all different. So I don't have anything to compare.

Is there any way to make it work?
Was This Post Helpful? 0
  • +
  • -

#6 JackOfAllTrades  Icon User is offline

  • Saucy!
  • member icon

Reputation: 6062
  • View blog
  • Posts: 23,513
  • Joined: 23-August 08

Re: PHP mysql multiple tables order by date

Posted 26 November 2011 - 12:08 PM

Please provide the schema for these two tables. Are these two tables related? Also describe the the data and what you're doing with it. Perhaps we can come up with some way to accomplish your goal. Running it through translate you seem to have two tables, member passwords and firm passwords. What are you trying to do with the data here?

EDIT: Ah, this is what you're trying to do:

Quote

Now I want to echo the column number from both tables ordered on the date of both tables.


Unfortunately, this doesn't make much sense to me. Column number ordered by date? Maybe provide us an example of the data and what you want it to look like.

This post has been edited by JackOfAllTrades: 26 November 2011 - 12:13 PM

Was This Post Helpful? 1
  • +
  • -

#7 localhost  Icon User is offline

  • D.I.C Head

Reputation: 5
  • View blog
  • Posts: 130
  • Joined: 13-December 10

Re: PHP mysql multiple tables order by date

Posted 26 November 2011 - 12:30 PM

I try to explain.


I got a table named 'ledenwachtwoordvergeten'.
Column 'nummer' has the rows:
- VlHtEEUEJV
- IZxvXZVb8y

Column 'geplaatst' has the rows:
- 2011-05-03 15:56:51
- 2011-02-20 16:12:14

So:

- VlHtEEUEJV 2011-05-03 15:56:51
- IZxvXZVb8y 2011-02-20 16:12:14


I got a table named 'bedrijvenwachtwoordvergeten'.
Column 'nummer' has the rows:
- 3pyTEK1UWH
- CUm44V7Ssj
- j8SH7b6FVL

Column 'geplaatst' has the rows:
- 2011-09-13 15:38:05
- 2011-04-05 16:53:34
- 2011-09-17 16:55:03

So:

- 3pyTEK1UWH 2011-09-13 15:38:05
- CUm44V7Ssj 2011-04-05 16:53:34
- j8SH7b6FVL 2011-09-17 16:55:03



Now I need to echo the nummer rows ordered on geplaatst DESC.

So I want to get the following result:

- IZxvXZVb8y (geplaatst = 2011-02-20 16:12:14)
- CUm44V7Ssj (geplaatst = 2011-04-05 16:53:34)
- VlHtEEUEJV (geplaatst = 2011-05-03 15:56:51)
- 3pyTEK1UWH (geplaatst = 2011-09-13 15:38:05)
- j8SH7b6FVL (geplaatst = 2011-09-17 16:55:03)

The echo part I can make by myself. I only need the sql command.
I hope you understand what I mean and what I want.

Thank you for the fast replys every time!
Was This Post Helpful? 0
  • +
  • -

#8 JackOfAllTrades  Icon User is offline

  • Saucy!
  • member icon

Reputation: 6062
  • View blog
  • Posts: 23,513
  • Joined: 23-August 08

Re: PHP mysql multiple tables order by date

Posted 26 November 2011 - 12:38 PM

OK, so they're not at all related, but have appear to have the same number of columns of the same datatype, so you might try a union:

SELECT ledenwachtwoordvergeten UNION bedrijvenwachtwoordvergeten ORDER BY geplaatst
.

Give that a try.
Was This Post Helpful? 0
  • +
  • -

#9 localhost  Icon User is offline

  • D.I.C Head

Reputation: 5
  • View blog
  • Posts: 130
  • Joined: 13-December 10

Re: PHP mysql multiple tables order by date

Posted 26 November 2011 - 12:41 PM

Nope.. It wont work..

I got the following code:
	$result = mysql_query("SELECT ledenwachtwoordvergeten UNION bedrijvenwachtwoordvergeten ORDER BY geplaatst");
	while($row = mysql_fetch_array($result)){
	echo $row['nummer'];
	echo "<br>";
	}



And I get the error:
Warning: mysql_fetch_array() expects parameter 1 to be resource


Was This Post Helpful? 0
  • +
  • -

#10 JackOfAllTrades  Icon User is offline

  • Saucy!
  • member icon

Reputation: 6062
  • View blog
  • Posts: 23,513
  • Joined: 23-August 08

Re: PHP mysql multiple tables order by date

Posted 26 November 2011 - 01:01 PM

Sorry, I gave you a bad query.

SELECT * FROM ledenwachtwoordvergeten UNION SELECT * FROM bedrijvenwachtwoordvergeten ORDER BY geplaatst


Try that, but it's been a long time since I've done any UNION queries, so it's possible the syntax still isn't quite right.
Was This Post Helpful? 2
  • +
  • -

#11 localhost  Icon User is offline

  • D.I.C Head

Reputation: 5
  • View blog
  • Posts: 130
  • Joined: 13-December 10

Re: PHP mysql multiple tables order by date

Posted 26 November 2011 - 01:07 PM

Thank you for the replys!

Unfortunately this also doesn't work...
Was This Post Helpful? 0
  • +
  • -

#12 hadi_php  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 10
  • View blog
  • Posts: 382
  • Joined: 23-August 08

Re: PHP mysql multiple tables order by date

Posted 26 November 2011 - 11:43 PM

you can try

ORDER by field_one,field_two
:)

also you can try Array system Like - you ll get two array then merge them then just sort them.
Was This Post Helpful? 0
  • +
  • -

#13 JackOfAllTrades  Icon User is offline

  • Saucy!
  • member icon

Reputation: 6062
  • View blog
  • Posts: 23,513
  • Joined: 23-August 08

Re: PHP mysql multiple tables order by date

Posted 27 November 2011 - 06:01 AM

Seems to work for me with a little test:

mysql> create table table1( user_pass varchar(255), created datetime not null);Query OK, 0 rows affected (0.04 sec)

mysql> create table table2( firm_pass varchar(255), created datetime not null);
Query OK, 0 rows affected (0.03 sec)

mysql> insert into table1 values('test1', now());
Query OK, 1 row affected (0.11 sec)

mysql> insert into table1 values('test2', now());
Query OK, 1 row affected (0.00 sec)

mysql> insert into table1 values('test3', now());
Query OK, 1 row affected (0.00 sec)

mysql> insert into table2 values('test21', now());
Query OK, 1 row affected (0.00 sec)

mysql> insert into table2 values('test22', now());
Query OK, 1 row affected (0.00 sec)

mysql> insert into table2 values('test23', now());
Query OK, 1 row affected (0.00 sec)

mysql> select * from table1 union select * from table2;
+-----------+---------------------+
| user_pass | created             |
+-----------+---------------------+
| test1     | 2011-11-27 07:59:48 |
| test2     | 2011-11-27 07:59:51 |
| test3     | 2011-11-27 07:59:54 |
| test21    | 2011-11-27 08:00:08 |
| test22    | 2011-11-27 08:00:11 |
| test23    | 2011-11-27 08:00:13 |
+-----------+---------------------+
6 rows in set (0.07 sec)

mysql> select * from table1 union select * from table2 order by created;
+-----------+---------------------+
| user_pass | created             |
+-----------+---------------------+
| test1     | 2011-11-27 07:59:48 |
| test2     | 2011-11-27 07:59:51 |
| test3     | 2011-11-27 07:59:54 |
| test21    | 2011-11-27 08:00:08 |
| test22    | 2011-11-27 08:00:11 |
| test23    | 2011-11-27 08:00:13 |
+-----------+---------------------+
6 rows in set (0.00 sec)



If you want more help, you will need to provide more information on how it's "not working".
Was This Post Helpful? 1
  • +
  • -

#14 localhost  Icon User is offline

  • D.I.C Head

Reputation: 5
  • View blog
  • Posts: 130
  • Joined: 13-December 10

Re: PHP mysql multiple tables order by date

Posted 28 November 2011 - 06:55 AM

Ok.. I figured out what was wrong.

If I use 2 tables that has EXACT the same columns it doesn't give a problem.
But if I want to select 2 tables that has different columns it doesn't give any result.

How can I get it working with tables that got different columns?

Thanks!

EDIT: I got that part working now... But how can I echo from which table it comes.
Example: I want the echo:
Result ### from table 1 at 12:00:00
Result ### from table 2 at 10:00:00
Result ### from table 1 at 09:00:00

So I want to echo from which table the result is.
How can I get this?

This post has been edited by localhost: 28 November 2011 - 07:04 AM

Was This Post Helpful? 0
  • +
  • -

#15 localhost  Icon User is offline

  • D.I.C Head

Reputation: 5
  • View blog
  • Posts: 130
  • Joined: 13-December 10

Re: PHP mysql multiple tables order by date

Posted 28 November 2011 - 07:53 AM

Thank you for all the help!

I fixed it by myself..

I use the while function to loop through the results.
In the loop I search through the database, if it gives a result I know from which table the result is.
This also works..

Thank you for all the help!
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1