[SOLVED] Insert 2 items from one table into another?

TechMistress · December 8, 2008

Hi All!

I have one table (cats) which has cat_id and cat_name

I have another table (projects) which posts the cat_id from a web form.

I also want to post the cat_name that goes with the cat_id into the (projects) table during this time.

I want to do this, since other parts of the site are just showing the cat_id number for a project, where I'd like it to show the cat_name.

I've created a new field in the (projects) table already called cat_name.

The form says to insert into projects the various fields, but I don't know how to pass the cat_name as well, since the form just passes the id.

Thanks!

rhodesa · December 8, 2008

this is the wrong way to approach this. instead, use a JOIN when requesting the data to get the cat_name

so, in projects, just have cat_id. then, when getting project info join like so:

SELECT * from projects p LEFT JOIN cats c ON p.cat_id = c.cat_id WHERE p.project_id = 123

that will return project info and cat info

TechMistress · December 8, 2008

I completely agree! This is a bit of an odd situation, however. I have this great search function with drop-downs. In the search results, I use the join to display the name instead of the id number.

However, at the beginning of the search function, the script calls on dropdowns from [field,table] - thus it just brings in the id. Here is the code:

/*------------------------------------------------------------------------
            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'])."%' ";
  }

  //add more elements (or take away) as you desire...follow the same code structure as above

   
  //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",($rows['project_start']));
$rows['project_end'] = date("n/j/y", ($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['cat_name']  ."</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

rhodesa · December 8, 2008

so, are you trying to add "cat_name" to the list of search dropdowns?

TechMistress · December 8, 2008

Yes, I'd like have the cat_name show instead of the id. In the part for the dropdowns, I put the category from projects, which is this:

$project_types = dropdown('project_types','projects');

If i change that to be the catname from the category list, of course it shows but it dosn't search it, since it's not from the projects table.

I need that project_types field (which is the category id) to be there, just not the number but the actual name.

This search script says later on how to populate the dropdown, which is field,table. I'm lost.

rhodesa · December 8, 2008

so...project_type is the cat_id?

so, do what you just said, and have it display a dropdown of distinct cat_names from the categories table. then, for the search, do

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

TechMistress · December 8, 2008

That won't work, since I have no where to post the cat name from.

Here is the process of the script:

1. create the dropdowns (simply display field, table)

2. When person selects dropdowns, it searches for those fields in the db table

3. Display results

Step three is where I used a join - to display the project_type from projects (cat id) and join it with the cat_name from categories.

The problem is, step one when the dropdowns are created, I can't display cat_name from categories, because then I'm bypassing the projects table (the heart of the search). Step 1, creating the dropdowns, is where I want to show the name that matches the project_types id - but the name is in another table.

Sheesh, sorry! I hope that wasn't confusing.

rhodesa · December 8, 2008

well...really, you should create your dropdown to have the values being the cat_id and the displayed text as the cat_name. the query to get that data should be:

SELECT p.cat_id, c.cat_name FROM projects p LEFT JOIN categories c ON p.cat_id = c.cat_id GROUP BY p.cat_id

TechMistress · December 8, 2008

Ok, so how would I do the construction of the dropdown field?

The section that says 'create dropdowns' near the top, uses the functions stated later in the section titled:

/*------------------------------------------------------------------------

create the drop downs

------------------------------------------------------------------------*/

I guess this is the area to specify that particular dropdown, but I don't know how to construct it there, and then call on it at the top in the 'create dropdowns' section. See what I mean? There's a specific formula it follows and I don't now how to do it without breaking the forumula.

rhodesa · December 8, 2008

try this for your modified function:

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

  //make sql
  if(empty($sql)){
    $sql = "SELECT $field FROM $table GROUP BY $field 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))
    {
      $title = $key = $rows[0];
      if(isset($rows[1]))
        $title = $rows[1];
      $oHTML .= "<option value='".$key."'>".$title."</option>\n";
    }
    $oHTML .= "</select>\n";
  }

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

and call it with:

$project_types   = dropdown('project_types','projects');
$project_state  = dropdown('project_state','projects');
$project_zip  = dropdown('project_zip','projects');
$project_city  = dropdown('project_city','projects');
$cat_ids = dropdown('cat_id',null,'SELECT p.cat_id, c.cat_name FROM projects p LEFT JOIN categories c ON p.cat_id = c.cat_id GROUP BY p.cat_id ORDER BY c.cat_name ASC');

TechMistress · December 8, 2008

I think we're getting closer...

I get this error:

Can't connect because Unknown column 'p.cat_id' in 'field list'

rhodesa · December 8, 2008

$cat_ids = dropdown('project_types',null,'SELECT p.project_types, c.cat_name FROM projects p LEFT JOIN categories c ON p.project_types = c.cat_id GROUP BY p.project_types ORDER BY c.cat_name ASC');
$project_state  = dropdown('project_state','projects');
$project_zip  = dropdown('project_zip','projects');
$project_city  = dropdown('project_city','projects');

TechMistress · December 8, 2008

Hmmm, no error now - but now dropdown, either.

See here: http://66.70.114.97/search.php

rhodesa · December 8, 2008

oops...

$project_types = dropdown('project_types',null,'SELECT p.project_types, c.cat_name FROM projects p LEFT JOIN categories c ON p.project_types = c.cat_id GROUP BY p.project_types ORDER BY c.cat_name ASC');
$project_state  = dropdown('project_state','projects');
$project_zip  = dropdown('project_zip','projects');
$project_city  = dropdown('project_city','projects');

TechMistress · December 8, 2008

Hey! Perfect! And it works great for the search, bringing back the right results.

Thank you so much! Hopefully, I'll be able to help someone in the future, too.

Sign In

[SOLVED] Insert 2 items from one table into another?

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information