[SOLVED] Display name from one table based on ID of another table

[TechMistress]

Hi all, I need a little help!

I have a page that displays the following:

 

title    category  link

 

The category reads as a number from the projects table.

The category name is in the categories table.

 

category = project_types in projects table  (ex. 25)

category name = cat_name in categories table (ex. Automotive)

cat_id (categories table) is the same as project_types (projects table)

 

How do I display the cat_name from one table as the project_type of my displayed page, so I don't see the number, but the name.

 

Here is my existing code:

 

The query:

$sql = "select * FROM projects where 1 ";

 

The result:

    while ($rows= mysql_fetch_array($result))

 

The display:

    <td align=center valign=top width=10%>". $rows[project_types]  ."</td>

 

project_types displays only the number

 

Thanks!

 

 

[Mchl]

USe query like this

 

SELECT projects.*, categories.cat_name FROM projects INNER JOIN categories ON (projects.project_types = categories.cat_id);

[TechMistress]

Ok, got the query in there. Do I need to change the output now? It still displays the number. I was wondering if I needed to do something like:

 

$rows['project_types'] = xxx

 

I also have a date field that looks like this in the db: 1228432870

I put in the following to display it in the format I like:

$rows['project_start'] = date("n/j/Y", strtotime($result['project_start']));

This displays the right format, but only shows today's date, not the date in the db.

 

(That's another problem, if anyone can help)

 

[Maq]

Seems like you're using 2 different arrays...  Where does $result[] come from?  It should be:

 

$rows['project_start'] = date("n/j/Y", strtotime($rows['project_start']));

 

FYI: 1228432870 is a timestamp.

[TechMistress]

The query and result is:

  $sql = "SELECT projects.*, categories.cat_name FROM projects INNER JOIN categories ON (projects.project_types = categories.cat_id) where 1";

  $result = conn($sql);

 

Then the display is as follows:

    while ($rows= mysql_fetch_array($result))
    {

$rows['project_start'] = date("n/j/Y", strtotime($result['project_start']));
$rows['project_end'] = date("n/j/Y", strtotime($result['project_start']));


      echo "<tr>";
      echo "
    <td valign=top width=30%><a href='project.php?id=". $rows[project_id]  ."'<strong>". $rows[project_title]  ."</strong></a></td>
    <td align=center valign=top width=10%>". $rows[project_bids]  ."</td>
    <td align=center valign=top width=10%>". $rows[project_types]  ."</td>
    <td align=center valign=top width=10%>". $rows[project_city]  ."</td>
    <td align=center valign=top width=10%>". $rows[project_state]  ."</td>
    <td align=center valign=top width=10%>". $rows[project_zip]  ."</td>
    <td align=center valign=top width=10%>". $rows[project_start]  ."</td>
    <td align=center valign=top width=10%>". $rows[project_end]  ."</td>
</tr>";
   
    } 

 

As for the timestamp, I just didn't know how to convert a timestamp into a date. I got the display to "look" correct, but it displays the wrong date.

 

 

[Maq]

Try this:

 

$sql = "SELECT projects.*, categories.cat_name FROM projects INNER JOIN categories ON (projects.project_types = categories.cat_id) where 1";
$result = mysql_query($sql);

while ($rows=mysql_fetch_array($result))
{
    $rows['project_start'] = date("n/j/Y", strtotime($rows['project_start']));
    $rows['project_end'] = date("n/j/Y", strtotime($rows['project_start'])); //Should be project_end?
    echo "";
    echo "
    ". {$rows['project_title']}  ."
    ". {$rows['project_bids']}  ."
    ". {$rows['project_types']}  ."
    ". {$rows['project_city']}  ."
    ". {$rows['project_state']}  ."
    ". {$rows['project_zip']}  ."
    ". {$rows['project_start']}  ."
    ". {$rows['project_end']}  ."
";
} 

[TechMistress]

Bummer, didn't work.  The category still displays the number, and then the date changed to the typical error date of 12/31/1969.

 

Here is the entire code of the page:

 

<?php
/*------------------------------------------------------------------------
            control codes
------------------------------------------------------------------------*/

if (isset($_POST['submit']))
{

  search();       //call the search function

}else{

  show_form();   //call the show form function

}//end if


/*------------------------------------------------------------------------
            show the search form
------------------------------------------------------------------------*/

function show_form()
{


  //call the dropdown function which creates an html string to build a select box for each element

  $project_types   = dropdown('project_types','projects');
  $project_state  = dropdown('project_state','projects');
  $project_zip  = dropdown('project_zip','projects');
  $project_city  = dropdown('project_city','projects');


  echo "<form name='search' action='".$_SERVER['PHP_SELF']."' method='post'>
<table width='100%' align='center' valign='center' class=content>
        <tr>
          <td align='left'>Search for projects that meet any number of criteria. In addition, you may <a href='/projects.php' target=_top><strong>View All Projects</strong></a>.
            <p> </p>
            <div align='left'>
              <table border='0' cellpadding='0' cellspacing='3' width='300'>
                <tr>
                  <td width='50%' align='left'><b>Project Category:</b></td>
                  <td width='50%'>$project_types</td>
                </tr>
                <tr>
                  <td width='50%' align='left'><b>Project State:</b></td>
                  <td width='50%'>$project_state</td>
                </tr>
                <tr>
                  <td width='50%' align='left'><b>Project Zip:</b></td>
                  <td width='50%'>$project_zip</td>
                </tr>
                <tr>
                  <td width='50%' align='left'><b>Project City:</b></td>
                  <td width='50%'>$project_city</td>
                </tr>
              </table>
            </div>
            <p><input type='submit' name='submit' value='Go!'></p>
          </td>
        </tr>
        </table>
        </form>";

}//end function


/*------------------------------------------------------------------------
            run the search and show the results
------------------------------------------------------------------------*/

function search()
{

//base sql
if ($_GET["sort"] == 1) {
$order = "project_title";
$sorted = "Project Title";
} else {
if ($_GET["sort"] == 2) {
	$order = "project_bids";
	$sorted = "Bids";
} else {
	if ($_GET["sort"] == 3) {
		$order = "project_category";
		$sorted = "Category";
	} else {
		if ($_GET["sort"] == 4) {
			$order = "project_city";
			$sorted = "City";
		} else {
			$order = "project_title";
			$sorted = "Project Title";
		}
	}
}
}
  $sql = "SELECT projects.*, categories.cat_name FROM projects INNER JOIN categories ON (projects.project_types = categories.cat_id) where 1";


//get the values from the form
//NOTE: You should do way more valdation on the values before you attempt to process anything

  if ((!empty($_POST['project_types']))&&($_POST['project_types'] != 'all'))
  {
    $sql .= " and project_types like '". addslashes($_POST['project_types'])."%' ";
  }

  if ((!empty($_POST['project_state']))&&($_POST['project_state'] != 'all'))
  {
    $sql .= " and project_state like '". addslashes($_POST['project_state'])."%' ";
  }

  if ((!empty($_POST['project_zip']))&&($_POST['project_zip'] != 'all'))
  {
    $sql .= " and project_zip like '". addslashes($_POST['project_zip'])."%' ";
  }

  if ((!empty($_POST['project_city']))&&($_POST['project_city'] != 'all'))
  {
    $sql .= " and project_city like '". addslashes($_POST['project_city'])."%' ";
  }

    
  //run query
  $result = mysql_query($sql);


  if (!$result){ die("No results due to database error.<br>".mysql_error());  }

  if (mysql_num_rows($result)==0)
  {
    echo "<span class=content>No Results found!<BR<BR><a href='javascript:history.go(-1)'><< Return to Search</a></span>";
  }else{

    echo"<table border=0 width=100% cellspacing=1 cellpadding=2>
  <tr>
    <td align=center class=tdListTitle bgcolor=#FFFFFF width=100% colspan=8>
      <p align=left><strong>  </strong><a href='javascript:history.go(-1)'><strong><< Return to Search</strong></a></p>
    </td>
  </tr>
  <tr>
    <td align=center class=tdListTitle width=30% bgcolor=#E1E1E1><strong>PROJECT NAME</strong></td>
    <td align=center class=tdListTitle width=10% bgcolor=#E1E1E1><strong>BIDS</strong></td>
    <td align=center class=tdListTitle width=10% bgcolor=#E1E1E1><strong>CATEGORY</strong></td>
    <td align=center class=tdListTitle width=10% bgcolor=#E1E1E1><strong>CITY</strong></td>
    <td align=center class=tdListTitle width=10% bgcolor=#E1E1E1><strong>STATE</strong></td>
    <td align=center class=tdListTitle width=10% bgcolor=#E1E1E1><strong>ZIP</strong></td>
    <td align=center class=tdListTitle width=10% bgcolor=#E1E1E1><strong>STARTED</strong></td>
    <td align=center class=tdListTitle width=10% bgcolor=#E1E1E1><strong>ENDS</strong></td>
  </tr>";

    while ($rows= mysql_fetch_array($result))
    {

$rows['project_start'] = date("n/j/Y", strtotime($rows['project_start']));
$rows['project_end'] = date("n/j/Y", strtotime($rows['project_end']));


      echo "<tr onmouseover=\"this.style.background='#E4E3F1';this.style.cursor='pointer' \" onmouseout=\"this.style.background='white'; \" onClick=\"project.php?id=". $rows[project_id]  ."\">";
      echo "
    <td valign=top width=30%><a href='project.php?id=". $rows[project_id]  ."'<strong>". $rows[project_title]  ."</strong></a></td>
    <td align=center valign=top width=10%>". $rows[project_bids]  ."</td>
    <td align=center valign=top width=10%>". $rows['project_types']  ."</td>
    <td align=center valign=top width=10%>". $rows[project_city]  ."</td>
    <td align=center valign=top width=10%>". $rows[project_state]  ."</td>
    <td align=center valign=top width=10%>". $rows[project_zip]  ."</td>
    <td align=center valign=top width=10%>". $rows[project_start]  ."</td>
    <td align=center valign=top width=10%>". $rows[project_end]  ."</td>
</tr>";
   
    } 

    echo "</table>";
  }//end if



}//end function

/*------------------------------------------------------------------------
            create the drop downs
------------------------------------------------------------------------*/

function dropdown($field, $table)
{ 
  //initialize variables
  $oHTML  = '';
  $result = '';

  //check to see if the field is passed correctly
  if (($field == "")||($table == ""))
  {
    die("No column or table specified to create drop down from!");
  }

  $sql = "select distinct($field) from $table ORDER BY $field ASC";

  //call the db function and run the query
  $result = conn($sql);

  //if no results are found to create a drop down return a textbox
  if ((!$result) ||(mysql_num_rows($result)==0))
  {
    $oHTML .= "<input type='text' name='$field' value='' size='15'>";
  }elseif (($result)&&(mysql_num_rows($result)>0)){
   
    //build the select box out of the results
    $oHTML .= "<select name='$field'>\n<option value='all'>All</option>\n";
    while ($rows = mysql_fetch_array($result))
    {
      $oHTML .= "<option value='".$rows[$field]."'>".$rows[$field]."</option>\n";
    }
    $oHTML .= "</select>\n";
  }

  //send the value back to the calling code
  return $oHTML;
}//end function

/*------------------------------------------------------------------------
            database connection function
------------------------------------------------------------------------*/
function conn($sql)
{   
  $username  = "x";
     $pwd      = "x";
     $host        = "x";
     $dbname      = "x";

    //echo "commencing connection to local db<br>";
   
    if (!($conn=mysql_connect($host, $username, $pwd)))  {
        printf("error connecting to DB by user = $username and pwd=$pwd");
        exit;
    }
    $db3=mysql_select_db($dbname,$conn) or die("Unable to connect to local database");
   
    $result = mysql_query($sql) or die ("Can't connect because ". mysql_error());
   
    return $result;
   
}//end function     

?>

[Maq]

Sorry, this will work...  (you don't need the strtotime() method)

 

$rows['project_start'] = date("n/j/Y", $rows['project_start']);
$rows['project_end'] = date("n/j/Y", $rows['project_end']);

[TechMistress]

Great! That worked perfect on the date.

 

Now I just need to figure out how to get the category name from categories to appear instead of the number from projects. The inner join didn't seem to affect anything. That's why I wondered if I had to specify it somewhere...

[Maq]

Now I just need to figure out how to get the category name from categories to appear instead of the number from projects.

 

Number of projects?  I don't see how that is being displayed.  Can you show me the relevant code?  To display the category just do:

 

$rows[cat_name]

 

That's why I wondered if I had to specify it somewhere...

 

You did, right here:

 

SELECT projects.*, categories.cat_name

[TechMistress]

It works great!

 

It was displaying the id#, not the number of projects. I changed it from

 

$rows['cat_name']

  to 

$rows['cat_name']

 

as you said, and it worked.

 

Thank you so much for your time, I truly appreciate it. It's nice to know that people can still help you out here.  You've just increased my knowledge!