PHP - Fetching Information From Multiple Tables And Put It On One Page

I am trying to make a filter to show posts from mutual friends between you and the persons profile you're on, I have somewhat of an idea on how to write the mysql query (it needs to be just one query, not two(which would be the easy way of doing it)).

the "friends" table has:
"friend_1" and "friend_2"

and the "posts" table:
"to_id" and "from_id"

The PHP variables would be:
"$session" and "$id"
id would be the users Id of the profile you're on.

Thanks, any help would be appreciated.

I have a search where I want to be able to search a string of words. The search is going to be looking in 2 different table joined by a left outer join. The tables are "quotes" and "categories".

$sql="SELECT q.id, q.username, q.quote, q.by, q.voteup, q.votedown, q.servtime, c.quote_id, c.label FROM quotes q LEFT OUTER JOIN categories c ON q.id = c.quote_id WHERE ( q.username LIKE '$srch' OR q.quote LIKE '$srch' OR q.`by` LIKE '$srch' OR c.label LIKE '$srch')";
   
The above mysql statement works and returns values..BUT if say, I search "john" and "funny", and a quote is posted by the user "john", but has a category of "funny" it will output the same quote twice. I was wondering if there was a way to see if a quote has either 1 term or both terms, if so display that quote but only display it once. Below is what the query is outputting.


[100] => Array
        (
            [id] => 100
            [username] => John
            [quote] => new test quote blah blah
            [by] => John
            [voteup] => 0
            [votedown] => 0
            [servtime] => 2010-12-02 @ 16:27:03
            [label] => Array
                (
                    [0] => Historic
                    [1] => Serious
                    [2] => Funny
                )

        )


Here is the code in full.


////
$sword = explode(" ",$search);
foreach($sword as $sterm){
$srch="%".$sterm."%";
echo"$srch<br />";
$sql="SELECT q.id, q.username, q.quote, q.by, q.voteup, q.votedown, q.servtime, c.quote_id, c.label FROM quotes q LEFT OUTER JOIN categories c ON q.id = c.quote_id WHERE ( q.username LIKE '$srch' OR q.quote LIKE '$srch' OR q.`by` LIKE '$srch' OR c.label LIKE '$srch')";
$result=mysql_query($sql);
while($row=mysql_fetch_object($result)){
$quote[$row->id]['id'] = $row->id;
$quote[$row->id]['username'] = $row->username;
$quote[$row->id]['quote'] = $row->quote;
$quote[$row->id]['by'] = $row->by;
$quote[$row->id]['voteup'] = $row->voteup;
$quote[$row->id]['votedown'] = $row->votedown;
$quote[$row->id]['servtime'] = $row->servtime;
$quote[$row->id]['label'][] = $row->label;
}
echo"<pre>";
print_r($quote);
echo"</pre>";


I don't think this is the fastest way of doing this, as it loops for each item in the db, per each keyword that is search.

Any help would be great!!
-BaSk

I am trying to export multiple csv files to download from 2 separate tables in my database.

Some background:
I have a web app that links to a phpmyadmin database. There are 2 tables in the database (entering and exiting). These tables hold the phone inventory information of employees currently entering or exiting the organization.

The fields in my tables a

location, firstname, lastname, username, extension, mac, type

What I am trying to do is export the data in these 2 tables to CSV (which I have working now) but I need to have multiple CSVs for each.

For example, for my exiting table, I need to have one CSV export all the fields in the table and I also need another CSV to export just the location and username field. I have a simple html form with a submit button which is currently working now:

<form action="exportexiting.php" method="POST" style="width: 456px; height: 157px">
   <div>
   <fieldset>
   <legend style="width: 102px; height: 25px"><strong>Entering CSV:</strong></legend>
   <input name="Export" type="submit" id="Export" value="Export">
   </fieldset><br/>
   </div>
</form>


This links to my exportexiting.php file:

<?php
$host = 'localhost';
$user = 'root';
$pass = 'xxxx';
$db = 'PhoneInventory';
$table = 'exiting';


 
// Connect to the database
$link = mysql_connect($host, $user, $pass);
mysql_select_db($db);
 
require 'exportcsv.inc.php';

 
exportMysqlToCsv($table);
 
 
 
?>

Then to exportcsv.inc.php:

<?php
 
function exportMysqlToCsv($table,$filename = 'export.csv')
{
    $csv_terminated = "\n";
    $csv_separator = ",";
    $csv_enclosed = '';
    $csv_escaped = "\\";
    $sql_query = "select * from $table";
 
    // Gets the data from the database
    $result = mysql_query($sql_query);
    $fields_cnt = mysql_num_fields($result);
 
 
    $schema_insert = '';
 
    for ($i = 0; $i < $fields_cnt; $i++)
    {
        $l = $csv_enclosed . str_replace($csv_enclosed, $csv_escaped . $csv_enclosed,
            stripslashes(mysql_field_name($result, $i))) . $csv_enclosed;
        $schema_insert .= $l;
        $schema_insert .= $csv_separator;
    } // end for
 
    $out = trim(substr($schema_insert, 0, -1));
    $out .= $csv_terminated;
 
    // Format the data
    while ($row = mysql_fetch_array($result))
    {
        $schema_insert = '';
        for ($j = 0; $j < $fields_cnt; $j++)
        {
            if ($row[$j] == '0' || $row[$j] != '')
            {
 
                if ($csv_enclosed == '')
                {
                    $schema_insert .= $row[$j];
                } else
                {
                    $schema_insert .= $csv_enclosed .
               str_replace($csv_enclosed, $csv_escaped . $csv_enclosed, $row[$j]) . $csv_enclosed;
                }
            } else
            {
                $schema_insert .= '';
            }
 
            if ($j < $fields_cnt - 1)
            {
                $schema_insert .= $csv_separator;
            }
        } // end for
 
        $out .= $schema_insert;
        $out .= $csv_terminated;
    } // end while
 
    header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
    header("Content-Length: " . strlen($out));
    // Output to browser with appropriate mime type, you choose
    header("Content-type: text/x-csv");
    //header("Content-type: text/csv");
    //header("Content-type: application/csv");
    header("Content-Disposition: attachment; filename=$filename");
    echo $out;
    exit;
 
}
 
?>



Again, this is working perfectly for only exporting all the data from the exiting table into one CSV file. My question is, how can I make my one submit button export the 2 CSV files that I need?


Thanks for the help...

I have a form where a user can duplicate part of a form like below:


I've named the fields with the HTML arrays (ie. "fieldName[]") and came up with this:

//--> CONTROLLERS
$system_controllers_qty = $_POST['system_controllers_qty'];
$system_controllers_make = $_POST['system_controllers_make'];
$system_controllers_model = $_POST['system_controllers_model'];
$system_controllers_serial_no         = $_POST['system_controllers_serial_no'];
for ($i = 0; $i < count($system_controllers_qty); $i++)
{
echo "qty " . clean($system_controllers_qty[$i]) . "<br/>";
echo "make " . clean($system_controllers_make[$i]) . "<br/>";
echo "model " . clean($system_controllers_model[$i]) . "<br/>";
echo "serial_no " . clean($system_controllers_serial_no[$i]) . "<br/>";
}

//--> CPUS
$system_cpus_qty = $_POST['system_cpus_qty'];
$system_cpus_model = $_POST['system_cpus_model'];
$system_cpus_serial_no = $_POST['system_cpus_serial_no'];
$system_cpus_speed = $_POST['system_cpus_speed'];
for ($i = 0; $i < count($system_cpus_qty); $i++)
{
echo "qty " . clean($system_cpus_qty[$i]) . "<br/>";
echo "model " . clean($system_cpus_model[$i]) . "<br/>";
echo "serial_no " . clean($system_cpus_serial_no[$i]) . "<br/>";
echo "speed " . clean($system_cpus_speed[$i]) . "<br/>";
}

//--> DISKS
$system_disks_qty = $_POST['system_disks_qty'];
$system_disks_make = $_POST['system_disks_make'];
$system_disks_model_no = $_POST['system_disks_model_no'];
$system_disks_size = $_POST['system_disks_size'];
$system_disks_serial_no = $_POST['system_disks_serial_no'];
for ($i = 0; $i < count($system_disks_qty); $i++)
{
echo "qty " . clean($system_disks_qty[$i]) . "<br/>";
echo "make " . clean($system_disks_make[$i]) . "<br/>";
echo "model_no " . clean($system_disks_model_no[$i]) . "<br/>";
echo "size " . clean($system_disks_size[$i]) . "<br/>";
echo "serial_no " . clean($system_disks_serial_no[$i]) . "<br/>";
}

//--> MEMORY
$system_memory_qty = $_POST['system_memory_qty'];
$system_memory_serial_no = $_POST['system_memory_serial_no'];
$system_memory_manf = $_POST['system_memory_manf'];
$system_memory_part_no = $_POST['system_memory_part_no'];
$system_memory_size = $_POST['system_memory_size'];
for ($i = 0; $i < count($system_memory_qty); $i++)
{
echo "qty " . clean($system_memory_qty[$i]) . "<br/>";
echo "serial " . clean($system_memory_serial_no[$i]) . "<br/>";
echo "manf " . clean($system_memory_manf[$i]) . "<br/>";
echo "part_no " . clean($system_memory_part_no[$i]) . "<br/>";
echo "size " . clean($system_memory_size[$i]) . "<br/>";
}


This just outputs all the fields I post at the moment. Say I have 3 different disk types, it's going to loop 3 times. Is it possible to make this all into 1 giant query or am I better off doing each query separately? Each section is a different table.

Hi,

So I am currently making a real estate site for my class at school but I am having a tiny issue with the listing display page.

So what I want to do is show a bunch of listing that the agent owns on their dashboard which I have already done. Now I have two links one to update the listing and one to view the listing. Now what I am trying to do is when the view listing link is clicked it will take the user to the listing-display page and then I want it to display all the information for the listing that the link was clicked under. But i cannot for the life of me figure out how to do that!

 

This is currently the code for the dashboard where you only view the listing headline

<?php

// If the session was never set with a user id
$output = '';
$conn = db_connect();
if($_SESSION['userType'] != AGENT)
{
    $_SESSION['RedirectError'] = "You were not logged in as an Agent<br/>";
    header("Location:login.php");
}
if (isset($_GET["page"])) {
    $page  = $_GET["page"];
    $index = ($page -1) * IMAGE_LIMIT;
}
else {
    $page=1;
    $index = 0;
 }


?>
  <!-- start of main page content -->
  <div class="container" style="margin-top: 2em;">
    <h2>Dashboard</h2>

      <p>Welcome back <?php echo $_SESSION['userId']; ?> you last logged in on <?php echo $_SESSION['last_access']; ?></p>
      <h4>Your Listings</h4>
      <?php
          $sql = "SELECT listing_id FROM listings WHERE user_id = '".$_SESSION['userId']."' AND status = 'o' ORDER BY listing_id";
          $result = pg_query($conn, $sql);
          $listings = pg_fetch_all($result);
          for($index; $index < pg_num_rows($result); $index++)
          {
              $sql = "SELECT * FROM listings WHERE listing_id = '".$listings[$index]['listing_id']."'";
              $listing_result = pg_query($conn, $sql);
              $arrayRow = pg_fetch_assoc($listing_result);
              echo (build_listing_card($arrayRow));
              if($index !=0 && ($index +1) % IMAGE_LIMIT ==0){
                  break;
              }
          }
      ?>
          </div>
          <br/>
          <?php
          $total_pages = ceil(count($listings) / IMAGE_LIMIT);
          $pageLink = "<div class='pagination'>";
          for ($i=1; $i<=$total_pages; $i++) {
                       if($i == $page)
                       {
                          $pageLink .= "<a class='active' href='dashboard.php?page=".$i."'>".$i."</a>";
                       }
                       else {
                           $pageLink .= "<a href='dashboard.php?page=".$i."'>".$i."</a>";
                       }
          };
           ?>
       </div>
       <br/>

and this is the code for the listing-display page where I want all the listing information to be displayed
 

<?php

if (isset($_GET['listing_id']))
    {
        $_SESSION['listing_id'] = $_GET['listing_id'];
    }
    else
    {
        $_SESSION['listing_id'] = 0;
        echo "ERROR: listing information was not find";
    }

    $sql = 'SELECT * FROM listings WHERE listing_id = ' . $_SESSION['listing_id'];
    echo "<BR>".$sql;
    $result = pg_query(db_connect(), $sql);
    $records = pg_num_rows($result);

    echo "<BR>".pg_fetch_result($result, 0, "listing_id");
    echo "<BR>".pg_fetch_result($result, 0, "user_id");
    echo "<BR>".pg_fetch_result($result, 0, "status");
    echo "<BR>".pg_fetch_result($result, 0, "price");
    echo "<BR>".pg_fetch_result($result, 0, "headline");
    echo "<BR>".pg_fetch_result($result, 0, "description");
    echo "<BR>".pg_fetch_result($result, 0, "postal_code");
    echo "<BR>".pg_fetch_result($result, 0, "images");
    echo "<BR>".pg_fetch_result($result, 0, "city");
    echo "<BR>".pg_fetch_result($result, 0, "property_options");
    echo "<BR>".pg_fetch_result($result, 0, "bedrooms");
    echo "<BR>".pg_fetch_result($result, 0, "bathrooms");
    echo "<BR>".pg_fetch_result($result, 0, "garage");
    echo "<BR>".pg_fetch_result($result, 0, "purchase_type");
    echo "<BR>".pg_fetch_result($result, 0, "property_type");
    echo "<BR>".pg_fetch_result($result, 0, "finished_basement");
    echo "<BR>".pg_fetch_result($result, 0, "open_house");
    echo "<BR>".pg_fetch_result($result, 0, "schools");
?>
<h1> Listing Display </h1>
<hr/>
<table style="width:50%" border="1px solid black">
    <tr>
        <th>Listing Image</th>
        <th>Listing Description</th>
    </tr>
    <tr>
        <th rowspan="2">
            <img src="" alt="Oshawa House" width="300px" height="200px"/>
        </th>

        <td align="left" valign="top">
        <h3><?php echo pg_fetch_result($result, 0, "headline")  ?></h3>
        <ul>
        <li>Open house?: <?php echo get_property('open_house', pg_fetch_result($result, 0, "open_house"))?> </li>
        <li>Finished Basement?: <?php echo get_property('finished_basement', pg_fetch_result($result, 0, "finished_basement"))?></li>
        <li><?php echo get_property('purchase_type', pg_fetch_result($result, 0, "purchase_type"))?></li>
        <li>Garage Type: <?php echo get_property('garage_type', pg_fetch_result($result, 0, "garage"))?></li>
        <li>Near school?: <?php echo get_property('schools', pg_fetch_result($result, 0, "schools"))?></li>
        <li>Status: <?php echo get_property('listing_status', strtolower(pg_fetch_result($result, 0, "status"))) ?></li>
        <li>Washrooms: <?php echo get_property('washrooms',pg_fetch_result($result, 0, "bathrooms"))  ?></li>
        <li>Bedrooms: <?php echo get_property('bedrooms',pg_fetch_result($result, 0, "bedrooms"))  ?></li>
        <li>Description: <?php echo pg_fetch_result($result, 0, "description")  ?></li>
        </ul>
        </td>

    </tr>
    <tr>
        <td align="left" valign="top"><ul>
            <li>Location: <?php echo get_property('cities',pg_fetch_result($result, 0, "city"))  ?></li>
            <li>Postal Code: <?php echo pg_fetch_result($result, 0, "postal_code") ?></li>
            <li>Price: <?php echo pg_fetch_result($result, 0, "price") ?></li>
            </ul>
        </td>
    </tr>

</table>
<form action="listing-matches.php">
<p>
<input type="submit" name="submit" value="Back"/></p>
</form>

 

 

Below are two pages...Post.php. and Posted.php the posted page checks and makes sure everything is filled out and makes sure that the link has not been already added. The problem I am having is that it is not pulling over the link information. Every time you hit submit it drops down and goes to saying that you forgot to fill something in and I have it showing everything right now so when that happens I can see what is not going through and the $link never comes through. Any ideas? if anyone needs a better explanation please post and I will try to explain better....basically I am trying to post and everything posts but the link.

POST.PHP
Code: [Select]
<h1>Post</h1>
<div class="descr"></div><form method="post" action="posted.php">
          <table align="center">
<tr>
            <td>
                    Youtube Embed Link
                    </td>
                    <td>
                    <input name="link" type="text" id="link"/>
                    </td>
                </tr>   
                <tr>
                <td>
                  Title Of Song
                    </td>
                    <td>
                    <input name="title" type="text" id="title" />
                    </td>
                </tr>
                <tr>
                <td>
                    Author
                    </td>
                    <td>
                    <input name="author" type="text" id="author" />
                    </td>
                </tr>
                <tr>
            <td>
                    Text:
                    </td>
                    <td>
                    <input name="information" type="text" id="information" />
                        <br />
                    </td>
                </tr>
                <tr>
                <td colspan="2" align="center">
                    <br>
                    <input name="submit" type="submit" value="Post" />
                    </td>
                </tr>               
            </table>
            </form>
POSTED.PHP
Code: [Select]
<?php
//msut be logged in page
session_start();

if  ($_SESSION['username'])
{
echo"";
}
else
die("You must log in first!");

//form information
$submit = $_POST['submit'];
$row4 = $_SESSION['username'];

// form data
$link = strip_tags($_POST['link']);
$information = strip_tags($_POST['information']);
$title = strip_tags($_POST['title']);
$author = strip_tags($_POST['author']);
$date = date('Y-m-d');


 //Connect To Database
$hostname='';
$username2='';
$password2='';
$dbname='';

  mysql_connect($hostname,$username2, $password2) OR DIE ('Unable to connect to database! Please try again later.');
mysql_select_db($dbname);
$querycheck = "SELECT * FROM videos WHERE username='$row4'";
$result = mysql_query($querycheck);
while($rowz = mysql_fetch_array($result))

$linkcheck = $rowz['link'];


if ($submit)
{
if ($link&&$information&&$title&&$author)
{
if ($link == $linkcheck)
{
die ("This link has already been posted.");
}
else

{


 //Connect To Database
$hostname='';
$username2='';
$password2='';
$dbname='';

 mysql_connect($hostname,$username2, $password2) OR DIE ('Unable to connect to database! Please try again later.');
mysql_select_db($dbname);
$queryreg = mysql_query("INSERT INTO videos Values ('','$row4','$link','$title','$author','$information','$date')");




}
}

else
{
die ("You forgot to put something in the link/title/author/text box.  $information $title $link $author");
     }

}


   


?>

Need help in updating the tables

I have array of data coming from a form which I'm inserting in my DB. I have 5 tables
product
filter
product_filter
heater
product_heater

Im able to put the data in the "product", "filter" & "heater" tables but i dont know how to put data inside the "product_filter" & "product_heater" table. Any help or direction to any tutorails is appreciated.

My Tables structu

product
id int(5)
product text
cost text
details text

filter
id int(5)
filter text
imgpath text

product_filter
id int(5)
id_product int(5)
id_filter int(5)

heater
id int(5)
heater text
imgpath text

product_heater
id int(5)
id_product int(5)
id_heater int(5)


Code: [Select]
// Product data Update
$name = mysql_real_escape_string($_POST['product']);
$cost = mysql_real_escape_string($_POST['cost']);
$details = mysql_real_escape_string($_POST['details']);

$sql_title = "INSERT INTO product (
id ,
product ,
cost ,
details ,
)
VALUES (
NULL , '$name' , '$cost' , '$details')";
if (!mysql_query($sql_title,$con))
  {
  die('Error: ' . mysql_error());
  }
echo "records for product added<br />";


// Filter update
// This is the array which is coming from the form
/*
Array ( [0] => ehiem
[1] => Hagan
[2] => Rena
[3] => jobo )


Array ( [0] => img1.jpg
[1] => img2.jpg
[2] => img3.jpg
[3] => img4.jpg )
*/

$filtername = mysql_real_escape_string($filtername);
$filterimgpath = mysql_real_escape_string($filterimg);
$combined_array = array_combine($filtername, $filterimgpath);
$values = array();
foreach ($combined_array as $filtername => $filterimgpath)
{
    $values[] = "('$filtername', '$filterimgpath')";
}
$sql = "INSERT INTO filter (filter , imgpath) VALUES " . implode(', ', $values);
//echo $lastid = mysql_insert_id()."<br />";
if (!mysql_query($sql,$con))
  {
  die('Error: ' . mysql_error());
  }
echo "records added<br />";

//Product Filter Update table
// This is where Im stuck. Not able to even think of anything....


// heater update
// This is the array which is coming from the form
/*
Array ( [0] => ehiem
[1] => Dolphin
[2] => Rena
[3] => jobo )


Array ( [0] => img1.jpg
[1] => img2.jpg
[2] => img3.jpg
[3] => img4.jpg )
*/

$heatername = mysql_real_escape_string($heatername);
$heaterimgpath = mysql_real_escape_string($heaterimg);
$combined_array = array_combine($heatername, $heaterimgpath);
$values = array();
foreach ($combined_array as $heatername => $heaterimgpath)
{
    $values[] = "('$heatername', '$heaterimgpath')";
}
$sql = "INSERT INTO heater (heater , imgpath) VALUES " . implode(', ', $values);
//echo $lastid = mysql_insert_id()."<br />";
if (!mysql_query($sql,$con))
  {
  die('Error: ' . mysql_error());
  }
echo "records added<br />";

//Product heater Update table
// This is where Im stuck. Not able to even think of anything....

I'm having a hard time grasping how to select data from several tables.

Here's the code

Code: [Select]
<?php 
require_once "config.php";
$gamedate = $_SESSION['date'];
echo "These umpires are not currently scheduled on this date, and have not asked for the day off:<br />";

$umpirelist = Array('umpire 1 name', 'umpire 2 name', 'umpire 3 name');

$dbc = mysql_pconnect($host, $username, $password);  
mysql_select_db($db,$dbc);  

foreach($umpirelist as $data) { 

//now get stuff from a table           
$sql = "SELECT calendar.date, calendar.ump1, calendar.ump2, calendar.ump3, calendar.ump4, calendar.ump5, vacation.date, vacation.umpire FROM umps, calendar, vacation WHERE umps.full_name = $data AND $gamedate = `calendar.date` AND $gamedate = `vacation.date` AND $data NOT IN ('calendar.ump1', 'calendar.ump2', 'calendar.ump3', 'calendar.ump4', 'calendar.ump5') AND $data NOT IN ('vacation.umpire') order by umps.full_name asc"; 

$rs = mysql_query($sql,$dbc);  
$matches = 0; 
while ($row = mysql_fetch_assoc($rs))  {
$matches++; 
echo "$row[$data]<br />";
}
}
?>

The table CALENDAR holds the date of the game, who is playing, and what umpires are schedule for that game.  it does have a unique "row_number" column as well, but didn't think I'd need it in this select.

The table VACATION has the date, and umpire's name that is on vacation.

The table UMPS contains the umpires name (same names as the array in the code) and email addresses and login info.

I'm getting this error:

Quote

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource on line 152



This is line 152

Quote

while ($row = mysql_fetch_assoc($rs))  {



Can someone please point me in the right direction as to where I'm going wrong?

Thanks!

Hi All,

I am adding a button that will delete many things in the system which all have the same $job_id

There is going to be 10 or so tables that need to delete *  where $job_id = ?

Is there a better way to do this rather than just iterating my code 10 times.

I was looking at just joining but isnt one benefit of prepared statements that they can be used again and again to increase speed.

How do I search through table 1, 2, 3 where a=x and b=y and c=z?

I hope this isn't a stupid question.  I have written a fairly large site using php and SQL.  The site has multiple features (how tos, classifieds, photos, videos, ect) with each section's data housed in its own table.  Now I would like to have one highlight from each section appear in boxes on the index page simutaniously. (basically returning one random item from each table when the page is loaded)  I know how to call each table individually with sql = mysql_query, however, I fear that calling the database that many times over and over in one page load could be problematic if the site begins to get a lot of traffic.   Am I correct in worring about overloading the server and is there a better way to do this?

$query = mysql_query("SELECT COUNT(tickets.id),tickets.id,tickets.status,tickets.date,tickets.question,tickets.title,users.position FROM tickets,users WHERE tickets.id='$existing' users.username='$username'");


How do I get it so it selects data from the table tickets where id='$existing', but I also want to get a user's (who is viewing the ID) position (rank). $username is a session. So, any thoughts on how I would do so?

My updated code:


<?php
session_start();
include("../includes/mysql.php");
include("../includes/config.php");

?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<link href="../style.css" rel="stylesheet" type="text/css" />
<title><?php echo $title; ?></title>
</head>

<body>
<div id="container">
<div id="content">
<div id="left">
<div class="menu">
<?php include("../includes/navigation.php"); ?>
<div class="menufooter"></div>
</div>
<?php include("../includes/menu.php"); ?>
</div>
<div id="middle">
<?php
$existing = $_POST['existing'];
$ip = $_SERVER['REMOTE_ADDR'];
$username = $_SESSION['user'];

$query = mysql_query("SELECT COUNT(tickets.id),tickets.id,tickets.status,tickets.date,tickets.question,tickets.title,users.position FROM tickets,users WHERE tickets.id='$existing' OR users.username='$username'");
$get = mysql_fetch_assoc($query);

if(!$existing)
{
echo 
'
<div class="post">
<div class="postheader"><h1>Error</h1></div>
<div class="postcontent">
<p>You have not enetered in a ticket ID. Please go back and do so.</p>
</div>
<div class="postfooter"></div>
</div>
';
}
elseif($get['COUNT(id)'] < 1)
{
echo 
'
<div class="post">
<div class="postheader"><h1>Error</h1></div>
<div class="postcontent">
<p>The ticket ID you are trying to use doesnt exist. Please go back or submit another ticket.</p>
</div>
<div class="postfooter"></div>
</div>
';
}
elseif($get['tickets.ip']==$ip || $get['user.position'] >= 1)
{
$status["tickets.status"]["0"] = "Waiting for support...";
$status["tickets.status"]["1"] = "Waiting for user...";
$status["tickets.status"]["2"] = "Ticket Closed...";
$status["tickets.status"]["3"] = "Ticket Opened...";

echo 
'
<div class="post">
<div class="postheader"><h1>View Ticket Status - ID '. $get["id"] .'</h1></div>
<div class="postcontent">
<p>Title: '. $get["tickets.title"] .' - Posted on: '. $get["tickets.date"] .'</p>
<p>Ticket Status: '. $status[$get["tickets.status"]] .'</p>
<p>Question: '. nl2br($get["tickets.question"]) .'</p>
</div>
<div class="postfooter"></div>
</div>
';

}
else
{
echo 
'
<div class="post">
<div class="postheader"><h1>Error</h1></div>
<div class="postcontent">
<p>This is not your ticket. You only have permission to view your tickets. Please go back.</p>
</div>
<div class="postfooter"></div>
</div>
';
}
?>
</div>
</div>
</div>
</body>
</html>

Hi, I want to be able to retrieve records from db tables to build an xml file. The problem I am having is that I don't know how to retrieve a value after using foreign key to get appropriate value. You see below I want to make a new attribute to store to var $newAttr.

Code: [Select]
<?php
$doc=new DOMDocument();
mysql_connect("localhost","root");
mysql_select_db("xmlDB");

$edges=mysql_query("SELECT * FROM edge");
while($row=mysql_fetch_assoc($edges)) {
if($row['flag']=='val') {
$curValue=mysql_query("SELECT val FROM value,edge WHERE val.edge_id=edge.edge_id");
if($curRowIsAttribute=mysql_query("SELECT is_Attribute FROM value,edge WHERE value.is_Attribute='Y' AND value.edge_id=edge.edge_id")) {
//create attribute node
$newAttr=$dom->createAttribute();//HELP HERE PLEASE THEN I SHOULD BE ABLE TO COMPLETE NEXT TWO LINES BELOW!

//set attribute value
...

//append attribute to current element
...

}//END IF
}//END IF
}//END BIG WHILE 
?>

Any help much appreciated!

I am working on a asset project to insert, delete and view assets (items) that i owe. I am having trouble in the insert part.

i have four tables

department (DeptID, DeptName) DeptID is the primary key in this table.

assetcategory (AssetCatID, AssestCategory) AssetCatID is the primary key in this table.

asset (AssetID, AssetDescription, EmpID, AssetCatID, DeptID, Model, Maker, SerialNUm, DateAguired) AssetID is the primary key here.

Employee (EmpID, FirstName, LastName) EmpID is the key here.

and this is the code i have to insert using php.
Code: [Select]
<?php 
if(isset($_POST['submit'])){

$FirstName = mysql_real_escape_string($_POST["FirstName"]);
$LastName = mysql_real_escape_string($_POST["LastName"]);
$DeptName = mysql_real_escape_string($_POST["DeptName"]);
$AssetCategory = mysql_real_escape_string($_POST["AssetCategory"]);
$Model = mysql_real_escape_string($_POST["Model"]);
$Maker = mysql_real_escape_string($_POST["Maker"]);
$SerialNum = mysql_real_escape_string($_POST["SerialNum"]);
$DateAguired = mysql_real_escape_string($_POST["DateAguired"]);
$AssetDescription = mysql_real_escape_string($_POST["AssetDescription"]);
$AssetCatID = mysql_real_escape_string($_POST["AssetCatID"]);
$AssetID = mysql_real_escape_string($_POST["AssetID"]);
$EmpID = mysql_real_escape_string($_POST["EmpID"]);
$DeptID = mysql_real_escape_string($_POST["DeptID"]);

if(empty($FirstName) || empty($LastName) || empty($DeptName) || empty($AssetCategory) || empty($Model) || empty($Maker) || empty($SerialNum) || empty($DateAguired) || empty($AssetDescription))
{
print "Please feel in all fields";
}
else
{
///insert into the department table..........
$query1= "INSERT INTO department VALUES (null,'{$DeptName}')";
$result1 = mysql_query($query1) or die(mysql_error());
$DeptID = mysql_insert_id();

//insert into the assetcategory table
$query2= "INSERT INTO assetcategory VALUES (null,'{$AssetCategory}')";
$result2 = mysql_query($query2) or die(mysql_error());
$AssetCatID = mysql_insert_id();

//insert into the assets table
$query3= "INSERT INTO assets VALUES (null,'{$AssetDescription}', '{$EmpID}','{$AssetCatID}','{$DeptID}','{$Model}','{$Maker}','{$SerialNum}','{$DateAguired}')";
$result3 = mysql_query($query3) or die(mysql_error());
$AssetCatID = mysql_insert_id();


print $query1;

}



}
else {
?>


the page is displaying this error: "Column 'AssetCatID' cannot be null"

I would need help in inserting into all table required data. any help? hope i am making sense