help in sql query

[kings]

hi

i want to fetch username from another table based on the id generated in the temopary table.

<?php
    @ $rpp;        //Records Per Page
    @ $cps;        //Current Page Starting row number
    @ $lps;        //Last Page Starting row number
    @ $a;        //will be used to print the starting row number that is shown in the page
    @ $b;         //will be used to print the ending row number that is shown in the page

  if(empty($_GET["cps"]))
    {          $cps = "0";          }
    else
    {          $cps = $_GET["cps"];      }
    $a = $cps+1;
    $rpp = "10";
    $lps = $cps - $rpp; //Calculating the starting row number for previous page
   if ($cps <> 0)
    {
        $prv =  "<a href='myagreement.php?cps=$lps"."&user1=".$_SESSION['employerid']."'>Previous</a>";
    }
    else   
    {
        $prv =  "<font color='cccccc'>Previous</font>";
    }
   mysql_query("DROP TABLE TEMPORARY IF EXISTS Mytemp");
   mysql_query("CREATE TEMPORARY TABLE Mytemp SELECT resumeid,ttdate,ttime from agreements WHERE buyerid='".$_SESSION['employerid']."' order by ttdate DESC");
   $q="Select SQL_CALC_FOUND_ROWS * from Mytemp limit $cps, $rpp ";
    $rs=mysql_query($q) or die(mysql_error());
    $nr = mysql_num_rows($rs); //Number of rows found with LIMIT in actiong r
    $q0="Select FOUND_ROWS()";
    $rs0=mysql_query($q0) or die(mysql_error());
    $row0=mysql_fetch_array($rs0);
    $nr0 = $row0["FOUND_ROWS()"]; //Number of rows found without LIMIT in action
    if (($nr0 < 10) || ($nr < 10))
    {
           $b = $nr0;     }
    else
    {
        $b = ($cps) + $rpp;     }
      ?>
<br>
<table border="0" cellpadding="0" cellspacing="0" width="90%" align="center">
<tr><td><b><font face="verdana" size=2></b></font><b><font face="verdana" size=2 color="#9999CC"></font></b></td></tr>
  <tr><td align=left colspan="2"><b><font face="verdana" size=1 color="#9999CC"><? echo "$nr0 Records Found"; ?></font></b></td></tr>
  <tr><td align='left' colspan="2"><b><font face="verdana" size=1 color="#9999CC"><? echo "Showing Records from $a to $b"; ?></font></b></td></tr>
</tr>
    <?
$i=$nr0;
    while ($row=mysql_fetch_array($rs))
    {
         $cps = $cps +1;
$idd=$row[2];
 $result=mysql_query("SELECT username from login where username='.$idd.'");
  while ($line=mysql_fetch_array($result))
    {
  echo "<tr>";
echo "<td>";
echo "<table  bgcolor='#fefefe' width='73%'  align='center' border='0'>";
 echo "<tr><td width='55%'><font face='verdana' size=1><strong>Resume ID : </strong></font><a href='agreement1.php?agreeid=".$row[0]."'>RB".$row[0]."</td><td width='25%'><font face='verdana' size=1><strong>Agreement Signed With</strong></font>$line[0]</td></tr>";
echo "<tr><td width='25%'><font face='verdana' size=1><strong>Date  </strong></font>".$row[1]."</td><td width='25%'><font face='verdana' size=1><strong>Time  </strong></font>".$row[2]."</td></tr>";
echo "</table>";
echo "</td>";
echo "</tr>";
  }
    echo "<tr><td align='right' colspan=2>$prv";
     if ($cps == $nr0)
    {         echo "  |  <font color='CCCCCC'>Next</font>";      }
    else
    {
        if ($nr0 > 5)
        {
            echo "  |  <a href='myagreement.php?cps=$cps&lps=$lps&user1=".$_SESSION['employerid']."'>Next</a>";
        }       }        ?>

[JRSofty]

How about reposting that and removing all of the PHP and HTML and just post the SQL statements that you want to run along with a description of the originating table.

This might help us to determine what the problem with your particular SQL queries are.

[kings]

SELECT post_resume.res_title, post_resume.candidate_type, post_resume.owner, post_resume.ind_type, post_resume.career, post_resume.resume, employer.association
FROM post_resume, employer
WHERE post_resume.ind_type = '".$industype."'
AND post_resume.career = '".$careerlevel."'
AND employer.association = 'cg'
AND post_resume.owner != '18'
ORDER BY tdate DESC

sorry for posting all the codes in sql.i want something like the above query.when i tried executing the above query ...all the rows are getting displayed three time.............please do tell me the error

[silviuks]

Use "GROUP BY" clause.

[kings]

i tried tat...it is showing error msg

SELECT post_resume.res_title, post_resume.candidate_type, post_resume.owner, post_resume.ind_type, post_resume.career, post_resume.resume, employer.association
FROM post_resume, employer
WHERE post_resume.ind_type = '".$industype."'
AND post_resume.career = '".$careerlevel."'
AND employer.association = 'cg'
AND post_resume.owner != '18'
ORDER BY tdate DESC group by employer.association

[silviuks]

i tried tat...it is showing error msg

SELECT post_resume.res_title, post_resume.candidate_type, post_resume.owner, post_resume.ind_type, post_resume.career, post_resume.resume, employer.association
FROM post_resume, employer
WHERE post_resume.ind_type = '".$industype."'
AND post_resume.career = '".$careerlevel."'
AND employer.association = 'cg'
AND post_resume.owner != '18'
ORDER BY tdate DESC group by employer.association

try:
SELECT post_resume.res_title, post_resume.candidate_type, post_resume.owner, post_resume.ind_type, post_resume.career, post_resume.resume, employer.association
FROM post_resume, employer
WHERE post_resume.ind_type = '".$industype."'
AND post_resume.career = '".$careerlevel."'
AND employer.association = 'cg'
AND post_resume.owner != '18'
GROUP BY employer.association
ORDER BY tdate DESC

[kings]

output
MySQL returned an empty result set (i.e. zero rows).

[jino]

U please check the query in PHP Myadmin wheather u r getting any values for ur query...

[kings]

ya i checked it in php myadmin only

[jino]

Try this by joining the tables using the INNER JOIN....

SELECT * FROM post_resume INNER JOIN employer
ON (post_resume.ind_type = '".$industype."'
AND post_resume.career = '".$careerlevel."'
AND employer.association = 'cg'
AND post_resume.owner != '18')
ORDER BY tdate DESC group by employer.association

Hope this will work!!

[kings]

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'group by employer.association

error

[JRSofty]

SELECT post_resume.res_title, post_resume.candidate_type, post_resume.owner, post_resume.ind_type, post_resume.career, post_resume.resume, employer.association
FROM post_resume, employer
WHERE post_resume.ind_type = '".$industype."'
AND post_resume.career = '".$careerlevel."'
AND employer.association = 'cg'
AND post_resume.owner != '18'
ORDER BY tdate DESC

sorry for posting all the codes in sql.i want something like the above query.when i tried executing the above query ...all the rows are getting displayed three time.............please do tell me the error

You'll need some sort of join to make this happen. You are attempting to get data from two different tables.

Is there data in the employer table like an ID that is placed into the post_resume table, so as to cross reference each other. For example:

Table1
tbl1_id tbl2_id tbl1_data

Table2
tbl2_id tbl2_data

Then you would need to create your query something like this

SELECT Table1.tbl1_data, Table2.tbl2_data
FROM Table1
JOIN Table2 ON Table1.tbl2_id = Table2.tbl2_id
WHERE tbl2_data = 'cg'

This of course is a very simplified version of what you are trying to do. Then you can add the ORDER BY statement to order it by what ever field.

If I were trying to fix your script it would look something like this (WARNING THIS MAY NOT WORK BECAUSE I DON'T KNOW EXACTLY WHAT YOUR DATABASE LOOKS LIKE).

SELECT post_resume.res_title, 
            post_resume.candidate_type, 
            post_resume.owner, 
            post_resume.ind_type, 
            post_resume.career, 
            post_resume.resume, 
            employer.association
FROM post_resume 
JOIN employer 
ON post_resume.employer_id = employer.employer_id
WHERE post_resume.ind_type = '".$industype."'
AND post_resume.career = '".$careerlevel."'
AND employer.association = 'cg'
AND post_resume.owner != '18'
ORDER BY post_resume.tdate DESC

Now you will notice in your ORDER BY statement I added a table name. This is because when you are working with multiple tables you need to specify the table where that column is found or else it is considered Ambiguous and the database will cause an error.

Again I don't know if the column employer_id actually exists but it shows that there must be something in common between the two tables.

[jino]

Ya exactly....

Inorder to get the result for the above query all the conditions should be satisfied...
As AND is used in between the conditions, the result will be displayed only if all the conditions are satisfying..

Inorder to check wheather ur query is correct or not .. Replace the AND with OR..

It is not the correct solution for the problem, but u can atleast check wheather ur query is correct or not..

good luck...
Jino.