PHP - Is One Submit Into Multiple Tables Possible?

Only the last row will update when I submit.

I have this form (and one nerve left):


<form action="" method="post" >

echo"
<input style=\"text-align:center\" type=\"text\" name=\"linkorder\" value=\"".$row['order']."\" />
<input type=\"hidden\" name=\"id\" value=\"".$row['id']."\"/>
";
<input type="submit" name="updateorder" value="Save Order" />
</form>


On Submit:


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

connect();
$changeorder = mysql_unbuffered_query("update links set `order` = '".$_POST['linkorder']."' where id = '".$_POST['id']."'") or die(mysql_error());

}



I was going put my fist through the monitor but then I remembered I could get help here.

Thank you!

    I have a webpage that I am working on that is now in construction mode. The url is: https://localbizseek.com/lbs/index6  The problem is that I want to it to be able to search 2 at the same time in the phpmyadmin database table which is named "places".  I also want to be able to get to search all 9 cities for that area.  There are a total of 36,000+ cities in the database.  However, I want it to be able to search just 2 or 9 specific cities of those cities.  Each city has a city_id which is an integer assigned to it in the database  Right now it will search only 1 city at a time or it will search every city in the database if I assign the search variable as "" or "0".  I don't know what to do on this page to solve it and on the related processing page.  Here is the code on the webpage:
   
              

        <form id="main-search-form" method="GET" action="<?= $baseurl; ?>/_searchresults.php" role="search">
         
        
        <select id="query-input" name="city_id" style="width:28.8%" style="height:69px" required>
                        
        <option value="">Select Area</option>
             
        <option value="3;1">Ches & VB</option>
        <option value="3">Chesapeake , VA</option>
        <option value="9">Hampton , VA</option> 
        <option value="10">Newport News , VA</option>   
        <option value="2">Norfolk , VA</option>   
        <option value="12">Poquoson , VA</option> 
        <option value="4">Portsmouth , VA</option>    
        <option value="5">Suffolk , VA</option>    
        <option value="1">Virginia Beach , VA</option> 
        <option value="11">Williamsburg , VA</option>
        <option value="0">All Active US Cities</option>
        </select>
        </form>

    You will notice that I have tried "3;1" up under the Ches & VB Select Option but that doesn't work because it will not work that way.  I can have it as "3 1" but then it just displays 1 which is VB's information.  I've tried everything possible and it won't search both variables.  It only will search one.
   
    So I'm thinking I need to do something at both ends on the code.  Here is the other end of the code which is the results processing page.  Note that there is also a third page of code which is the template page which I will add next:
   
             

      <form id="main-search-form" method="GET" action="<?= $baseurl; ?>/_searchresults.php" role="search">
         
        
          <select id="query-input" name="city_id" style="width:28.8%" style="height:69px" required>
                        
              <option value="">Select Area</option>
             
              <option value="3,1">Ches & VB</option>
              <option value="3">Chesapeake , VA</option>
        <option value="9">Hampton , VA</option> 
        <option value="10">Newport News , VA</option>   
        <option value="2">Norfolk , VA</option>   
        <option value="12">Poquoson , VA</option> 
        <option value="4">Portsmouth , VA</option>    
                    <option value="5">Suffolk , VA</option>    
                    <option value="1">Virginia Beach , VA</option> 
        <option value="11">Williamsburg , VA</option>
                    <option value="0">All Active US Cities</option>
   
      
           
        </select>

    Here is the final page of code which I'm not sure if this one needs changing but just in case here it is:
   

    <!DOCTYPE html>
    <!--[if IE 9]><html class="lt-ie10" lang="<?= $html_lang; ?>"> <![endif]-->
    <html class="no-js" lang="<?= $html_lang; ?>">
    <head>
    <title><?= $txt_html_title; ?> - <?= $site_name; ?></title>
    <meta name="description" content="<?= $txt_meta_desc; ?>" />
    <meta name="robots" content="noindex">
    <?php require_once('_html_head.php'); ?>
    </head>
    <body class="tpl-searchresults">
    <?php require_once('_header.php'); ?>
   
     <h1><?= $txt_main_title; ?></h1>
   
     <div class="full-block">
      <div class="content-col">
       <div class="list-items">
        <?php
        /*
        --------------------------------------------------
        BEGIN SHOW LIST
        --------------------------------------------------
        */
        if($total_rows > 0) {
         $count = ($page - 1) * $limit;
         $results_arr = array();
   
         foreach($list_items as $k => $v) {
          $count++;
          $place_id         = $v['place_id'];
          $place_name       = $v['place_name'];
          $place_slug       = $v['place_slug'];
          $address          = $v['address'];
          $cross_street     = $v['cross_street'];
          $place_city_name  = $v['place_city_name'];
          $place_city_slug  = $v['place_city_slug'];
          $place_state_abbr = $v['place_state_abbr'];
          $postal_code      = $v['postal_code'];
          $area_code        = $v['area_code'];
          $phone            = $v['phone'];
                                                    $phone = preg_replace("/[^0-9]/", "", $phone);
                                                    $phone = substr($phone, 0, 3) . '-' . substr($phone, 3);
          $lat              = $v['lat'];
          $lng              = $v['lng'];
          $cat_icon         = $v['cat_icon'];
          $photo_url        = $v['photo_url'];
          $rating           = $v['rating'];
          $description      = $v['description'];
   
          $results_arr[] = array(
           'ad_id'    => $place_id,
           'ad_lat'   => $lat,
           'ad_lng'   => $lng,
           'ad_title' => $place_name,
           'count'    => $count,
           'cat_icon' => $cat_icon);
          $places_names_arr[] = $place_name;
          ?>
          <div class="item" data-ad_id="<?= $place_id; ?>">
           <div class="item-pic" id="<?= $place_id; ?>">
            <img src="<?= $photo_url; ?>" />
           </div><!-- .item-pic -->
   
           <div class="item-description">
            <div class="item-title-row">
             <div class="item-counter"><div class="item-counter-inner"><?= $count; ?></div></div>
   
             <h2><a href="<?= $baseurl; ?>/<?= $place_city_slug; ?>/place/<?= $place_id; ?>/<?= $place_slug; ?>" title="<?= $place_name; ?>"><?= $place_name; ?></a></h2>
            </div>
            <div class="item-ratings-wrapper">
             <div class="item-rating" data-rating="<?= $rating; ?>">
              <!-- raty plugin placeholder -->
             </div>
             <div class="item-ratings-count">
              <?php // echo $count_rating; ?> <?php // echo ($count_rating == 1 ? 'review' : 'reviews'); ?>
             </div>
             <div class="clear"></div>
            </div><!-- .item-ratings-wrapper -->
            <div class="item-info">
             <div class="item-addr">
              <strong>
               <?= (!empty($address)) ? $address : ''; ?>
              </strong>
              <?= (!empty($cross_street)) ? "($cross_street)" : ''; ?>
              <br>
              <strong>
               <?= (!empty($place_city_name))  ? "$place_city_name," : ''; ?>
               <?= (!empty($place_state_abbr)) ? " $place_state_abbr " : ''; ?>
               <?= (!empty($postal_code))      ? $postal_code : ''; ?>
              </strong>
             </div>
   
             <div class="item-phone">
              <?= (!empty($phone)) ? '<i class="fa fa-phone-square"></i>' : ''; ?>
              <?= (!empty($area_code)) ? $area_code : ''; ?>
              <?= (!empty($phone)) ? $phone : ''; ?>
             </div>
            </div><!-- .item-info -->
   
            <?php
            echo (!empty($tip_text)) ? $tip_text : '';
            ?>
           </div><!-- .item-description -->
   
           <div class="clear"></div>
          </div><!-- .item  -->
          <?php
         } //  end foreach($response as $k => $v)
        } // end if($total_rows > 0)
       else {
        // else no results found
        ?>
        <div class="empty-cat-template">
         <p><?= $txt_empty_results; ?></p>
        </div>
        <?php
       }
       ?>
       </div><!-- .list-items -->
   
       <?php
       /*
       --------------------------------------------------
       BEGIN PAGER
       --------------------------------------------------
       */
       ?>
       <div id="pager">
        <ul class="pagination">
         <?php
         if(!empty($pager) && $pager->getTotalPages() > 1) {
          $curPage = $page;
   
          $startPage = ($curPage < 5)? 1 : $curPage - 4;
          $endPage = 8 + $startPage;
          $endPage = ($pager->getTotalPages() < $endPage) ? $pager->getTotalPages() : $endPage;
          $diff = $startPage - $endPage + 8;
          $startPage -= ($startPage - $diff > 0) ? $diff : 0;
   
          $startPage = ($startPage == 1) ? 2 : $startPage;
          $endPage = ($endPage == $pager->getTotalPages()) ? $endPage - 1 : $endPage;
   
          if($total_rows > 0) {
           $page_url = "$baseurl/_searchresults.php?city_id=$query_city_id&query=$query_query&page=";
   
           if ($curPage > 1) {
            ?>
            <li><a href="<?= $page_url; ?>1">Page 1</a></li>
            <?php
           }
   
           if ($curPage > 6) {
            ?>
            <li><span>...</span></li>
            <?php
           }
   
           if ($curPage == 1) {
            ?>
            <li class="active"><span>Page 1</span></li>
            <?php
           }
   
           for($i = $startPage; $i <= $endPage; $i++) {
            if($i == $page) {
             ?>
             <li class="active"><span><?= $i; ?></span></li>
             <?php
            }
            else {
             ?>
             <li><a href="<?php echo $page_url, $i; ?>"><?= $i; ?></a></li>
             <?php
            }
           }
   
           if($curPage + 5 < $pager->getTotalPages()) {
            ?>
            <li><span>...</span></li>
            <?php
           }
   
           if($pager->getTotalPages() > 5) {
            $last_page_txt = "Last Page";
           }
   
           $last_page_txt = ($pager->getTotalPages() > 5) ? "Last Page" : $pager->getTotalPages();
   
           if($curPage == $pager->getTotalPages()) {
            ?>
            <li class="active"><span><?= $last_page_txt; ?></span></li>
            <?php
           }
           else {
            ?>
            <li><a href="<?php echo $page_url, $pager->getTotalPages(); ?>"><?= $last_page_txt; ?></a></li>
            <?php
           }
          } //  end if($total_rows > 0)
         } //  end if(isset($pager) && $pager->getTotalPages() > 1)
   
         if(isset($pager) && $pager->getTotalPages() == 1) {
          ?>
   
          <?php
         }
         ?>
        </ul>
       </div><!-- #pager -->
      </div><!-- .content-col -->
      
   
      <div class="sidebar"><div class="clear"></div>
       <?php
       if($total_rows > 0) {
        ?>
          <div class="clear"></div>
        <div class="map-wrapper" id="sticker">
         <div id="map-canvas" style="width:100%; height:100%"></div>
        </div>
        <?php
       }
       ?>
      </div>
       <div class="clear"></div>
      
      
      <!-- #sidebar -->
   
      <div class="clear"></div>
     </div><!-- .content-full -->
   
    <?php require_once('_footer.php'); ?>
    <script type="text/javascript" src="https://maps.googleapis.com/maps/api/js?key=<?= $google_key; ?>"></script>
   
    <script type="text/javascript">
    // place markers
    var results_obj = <?php echo json_encode($results_arr); ?>;
    var infowindow;
    var map;
   
    function initialize() {
     markers = {};
     infoboxcontents = {};
   
     // set map options
     var mapOptions = {
      zoom: 1
     };
   
     // instantiate map
     var map = new google.maps.Map(document.getElementById('map-canvas'), mapOptions);
     var bounds = new google.maps.LatLngBounds();
     var infowindow = new google.maps.InfoWindow();
   
     // $results_arr[] = array("ad_id" => $place_id, "ad_lat" => $ad_lat, "ad_lng" => $ad_lng, "ad_title" => $ad_title, "count" => $count);
   
     // set markers
     for (var k in results_obj) {
      var p = results_obj[k];
      var latlng = new google.maps.LatLng(p.ad_lat, p.ad_lng);
      bounds.extend(latlng);
   
      var marker_icon = '<?= $baseurl; ?>/imgs/marker1.png';
   
      // place markers
      var marker = new google.maps.Marker({
       position: latlng,
       map: map,
       animation: google.maps.Animation.DROP,
       title: p.ad_title,
       //icon: marker_icon
      });
   
      markers[p.ad_id] = marker;
      infoboxcontents[p.ad_id] = p.ad_title;
   
      // click event on markers to show infowindow
      google.maps.event.addListener(marker, 'click', function() {
       infowindow.setContent(this.title);
       infowindow.open(map, this);
      });
     } // end for (var k in results_obj)
   
     map.fitBounds(bounds);
   
     $(".list-items .item").mouseover(function() {
      marker = markers[this.getAttribute("data-ad_id")];
      // mycontent = infoboxcontents[this.getAttribute("data-ad_id")];
   
      mycontent =  '<div class="scrollFix">' + infoboxcontents[this.getAttribute("data-ad_id")] + '</div>';
      // console.log(mycontent);
   
      infowindow.setContent(mycontent);
      // infowindow.setOptions({maxWidth:300});
      infowindow.open(map, marker);
      marker.setZIndex(10000);
     }); // end mouseover
    } //  end initialize()
   
    google.maps.event.addDomListener(window, 'load', initialize);
    </script>
   
    <script type="text/javascript">
    $.fn.raty.defaults.path = '<?= $baseurl; ?>/templates/lib/raty/images';
    $('.item-rating').raty({
     readOnly: true,
     score: function(){
      return this.getAttribute('data-rating');
     }
    });
    </script>
    <script>
    $(document).ready(function(){
     $("#sidebar").sticky({topSpacing:0});
    });
    </script>
   
    <script>
      $(document).ready(function(){
        $("#sticker").sticky({topSpacing: 24});
      });
    </script>
    <script type="text/javascript">
    function getUrlVars() {
        var vars = [], hash;
        var hashes = window.location.href.slice(window.location.href.indexOf('?') + 1).split('&');
        for(var i = 0; i < hashes.length; i++) {
            hash = hashes.split('=');
            vars.push(hash[0]);
            vars[hash[0]] = hash[1];
        }
        return vars;
    }
   
    $(document).ready(function() {
     var loc = '<?= e($_GET['loc']); ?>';
     var query = '<?= e($_GET['query']); ?>';
     if(loc !== '') {
      $("#city-input").val(loc);
     }
     if(query !== '') {
      $("#query-input").val(query);
     }
    });
   
    </script>
    </body>
    </html>

 

Edited April 13, 2019 by requinix
please use the Code <> button when posting code.

Hi All,

I'm hoping someone can help me. I'm looking to create a form that will submit multiple entries to a MySQL database depending on the options selected on the form. I am able to submit single entries, but the multiple entire is a bit of a mystery to me.

I have attached an JPG image to show an example of how I would like the form to work.

I have also added the description/explination below to this example:

Example 1            
The form was completed and three access types were selected. Thus Three entries, all with the Same Reference number were captured into the database   
         
Example 2            
The form was completed and Two Access Types were selected. Thus Two entries, all with the Same Reference number were captured into the database            
Example 3            
The form was completed and Four Access Types were selected. Thus Four entries, all with the Same Reference number were captured into the database      

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?

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....

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.

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

hi im trying to echo out all the possibilities from one column in 2 different tables, both with the same structure. if that makes sense

this is what i have

$query = mysql_query("SELECT * FROM stanjamesprem,centrebetprem GROUP BY eventname ORDER BY eventtime ");
while($row = mysql_fetch_assoc($query))
   {
   echo $row[eventname];
   }

if i take one of the tables out it works fine but if i have 2 table names it doenst work

please help me

cheers matt

any questions please ask away

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, 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!

$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'm very new to PHP and have managed to get through most of the problems I've encountered so far, but I have got to one point that I can't seem to solve.

I have two mysql tables (Categories & Products), I have a page that shows the categories (generated from the categories table) and when a category is selected it lists the products in that category.

Due to stock etc. not all categories always have products associated to them.

The Category & Product Tables are automatically updated, so what I need to do is only show categories that have products associated to them, both tables have a column category_no, and nothing I've tried so far seems to work.

The code I have so far is:-

Code: [Select]
Require_once('config.php');

Mysql_connect(db_host, db_user, db_password);

@mysql_select_db(db_database) or die( "unable to select database");

$query="select * from categories";

$result=mysql_query($query);

$num=mysql_numrows($result);

Mysql_close();
Thanks

Pete

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

Hey,

I have a query running inner joins... which currently works, but I need some more information pulled from other tables...

Code: [Select]
define('WPSC_TABLE_PRODUCT_LIST', "{$wp_table_prefix}wpsc_product_list");
define('WPSC_TABLE_PRODUCTMETA', "{$wp_table_prefix}wpsc_productmeta");
define('WPSC_TABLE_CATREF', "{$wp_table_prefix}wpsc_item_category_assoc");
define('WPSC_TABLE_CATNAME', "{$wp_table_prefix}wpsc_product_categories");


$cf="Linked Products";
$sku = get_post_meta($post->ID, $cf, true);
$array = explode(",",$sku);

$products = array_count_values($array);

foreach($products as $key => $value) {
$product = $wpdb->get_row("SELECT meta.product_id AS pid, list.name AS name, list.price AS price, retailer.category_id AS catid FROM ".WPSC_TABLE_PRODUCTMETA." AS meta INNER JOIN ".WPSC_TABLE_PRODUCT_LIST." AS list ON ( list.id = meta.product_id ) INNER JOIN ".WPSC_TABLE_SHOPNAME." AS retailer ON ( retailer.product_id = meta.product_id ) WHERE `meta_key` IN ( 'sku' ) AND `meta_value` IN ( '{$key}' ) ORDER BY list.id DESC");
?>
But I also need the category name - So I have to cross reference the following...

wordpdem_wpsc_product_list

id      name
433   itemone
432   itemtwo
431   itemthree

wordpdem_wpsc_item_category_assoc

id   product_id    category_id
1    433                    1
2    432                    2
3    410                    3

wordpdem_wpsc_product_categories

id  name
1   Bread
2   Fish
3   Snacks

I've now written:
Code: [Select]
SELECT
meta.product_id AS pid,
list.name AS name,
list.price AS price,
retailer.category_id AS catid,
category.name AS catname

FROM wordpdem_wpsc_productmeta AS meta
INNER JOIN wordpdem_wpsc_product_list AS list ON ( list.id = meta.product_id )
INNER JOIN wordpdem_wpsc_item_category_assoc AS retailer ON ( retailer.product_id = meta.product_id )
INNER JOIN `wordpdem_wpsc_product_categories``wordpdem_wpsc_product_list` AS category ON ( retailer.category_id = category.id )
WHERE `meta_key` IN ( 'sku' ) AND `meta_value` IN ( '{$key}' ) ORDER BY list.id DESC
However it fails on line 12 (the Where statement...)

Any ideas what I've done wrong?