query in mysql

[lydia21]

html page
<form method="get" action="sort2.php">
<table>
<tr>
<td>id</td><td><input type="text" name="id"></td>
</tr>
<tr>
<td>From</td><td><input type="text" name="from"></td>
<td>To</td><td><input type="text" name="to"></td>
<td><input type="submit" name="submit" value="submit"></td></tr>
</table>
</form>

sort2.php

<?php
$id=$_REQUEST;
$to=$_REQUEST;
$from=$_REQUEST;
$link=mysql_connect($hostname, $username, $password);
mysql_select_db($dbid) or die("unable to connect");

$query="(select name,id from member where id=(select day,time from attend where day between '$from' and '$to')";

$result = mysql_query($query) or die ('Error in query: $query. ' . mysql_error());
echo "<table width='90%'>";

echo "<tr><td bgcolor='#008080'><font face='Times New Roman' color='#FFFFFF' size='2'><strong>Day</strong></font> </td>
<td bgcolor='#008080'><font face='Times New Roman' color='#FFFFFF' size='2'><strong>Name</strong></font> </td>
<td bgcolor='#008080'><font face='Times New Roman' color='#FFFFFF' size='2'><strong>Time</strong></font> </td></tr>";
if (mysql_num_rows($result) > 0 )
{

while($row = mysql_fetch_row($result))
{

echo "<tr>";

echo "<td>".$row[0]."</td><td>".$row[1]."</td><td> ".$row[2]."</td>";
echo "</tr>";
echo "<tr><td bgcolor='#008080'></td><td bgcolor='#008080'></td><td bgcolor='#008080'></td></tr>";
}
echo "</table>";
}

mytable

tb1
id(primary key)
name....

tb2

day,time,id........

please tell me how to fetch the name,day,time from the table and display

[nav33n]

Your query is wrong for 2 reasons.
1. The sub query returns more than 1 record. eg. where id = (1,2,3,4) isn't valid. You should use IN clause.
2. The sub query should return only id. You are trying to fetch day and time, which fails query 1. Anyway, your query should be

SELECT t1.name, t2.day,t2.time
  FROM table1 AS t1 INNER JOIN table2 AS t2 ON t1.id = t2.id where t2.day between '$from' and '$to';

And please dont create duplicate threads. :)

Cheers,
Naveen

[lydia21]

hi
it not fetching any data from the table

[nav33n]

hmm.. my suggestion is, go to phpmyadmin, export the table structure and post them here.. (both the tables). This way, it would be easier for us to know what exactly you want and what is the table structure.

[lydia21]

member table is for creating new user
-- Table structure for table `member`
--

CREATE TABLE `member` (
`spid` varchar(50) NOT NULL,
`uname` varchar(75) NOT NULL,
`pss` varchar(50) NOT NULL,
`type` varchar(50) NOT NULL,
`name` varchar(50) NOT NULL,
`desecn` varchar(75) NOT NULL,
`datej` date NOT NULL,
`project` varchar(100) NOT NULL,
PRIMARY KEY (`spid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `attend` (
`spid` varchar(20) NOT NULL,
`day1` date NOT NULL,
`time1` varchar(20) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

[nav33n]

I created the table, inserted some values to the tables. I used the following queries and i got the output.

SELECT t1.name, t2.day1, t2.time1
FROM member AS t1
INNER JOIN attend AS t2 ON t1.spid = t2.spid
WHERE t2.day1
BETWEEN '2007-12-01'
AND '2007-12-10'
LIMIT 0 , 30

This is the output that i got.

name |	day1 |	time1
-------------------------------
aaa | 2007-12-04 | 	 
bbb | 2007-12-03 |	 
ddd | 2007-12-02 |	 
ddd | 2007-12-02 |	 
fff  | 2007-12-02 |

I am querying the table to return only those rows where day1 is between dec 1st and dec 10th and return only those rows where spid in table 1 is equal to spid in table2. I wonder why it works for me and not you ! Check if you have any entries in tables. Also check how you are passing the variables $from and $to. Since its a date field, If your format is wrong, it will not return anything.
I hope this helps :)

[lydia21]

$tomorrow1 = mktime(0,0,0,date("m"),date("d")+1,date("Y"));
echo "".date("Y/m/d", $tomorrow1);

$link=mysql_connect($hostname, $username, $password);
mysql_select_db($dbid) or die("unable to connect");

echo "<table width='60%'>";
$result=mysql_query("SELECT leaveid,lname,date2 FROM applyleave where date3=$tomorrow1");

print "<tr><td bgcolor='#008080'><font face='Times New Roman' color='#FFFFFF' size='2'><strong>Name</strong></font> </td>
<td bgcolor='#008080'><font face='Times New Roman' color='#FFFFFF' size='2'><strong>Date</strong></font> </td></tr>";

for ($i = 0; $i < mysql_num_rows($result); ++$i)
{

$line = mysql_fetch_row($result);
print "<tr><td><a href='viewleave1.php?leaveid=".$line[0]."'>".$line[1]."</td><td>".$line[2]."</td></tr>";
print "<tr><td bgcolor='#008080'></td><td bgcolor='#008080'></td></tr>";

}

hi query is working for sql...but in php it not displaying anything is my query right in php

[nav33n]

$result=mysql_query("SELECT leaveid,lname,date2 FROM applyleave where date3=$tomorrow1");

The query is correct. but its missing ''. $tomorrow1 should be in ''. The best way is to assign the query to a variable and then use mysql_query. Eg.

$query = "SELECT leaveid,lname,date2 FROM applyleave where date3='$tomorrow1'"; 
$result=mysql_query($query);

This way, if your query isn't working, you can print $query, execute it in phpmyadmin and then debug the error.

Cheers,
Naveen

[lydia21]

it is not working..
i tried it as
echo $tomorrow1;

output is
1196985600

i want to know whether i should type cast...

[nav33n]

yeah.. thats unixtimestamp.. $tomorrow1=date("Y-m-d",$tomorrow1); That will convert it to the date format yyyy-mm-dd.