PHP - Query Multiple Tables...
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? Similar Tutorials$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 have a database with multiple records. One of those values is an image. When i query the database and i want only the images as a result it gives the result in a field. It then fills up the field horizontal and when there is no more field left it starts a new row. That is what i want. But now i want some extra info next to the picture so i put that in a table with two colums like this: echo '<table border="1" width="400"><td><a href="'.$row['website'].'"> <img src="'.$row['prod_img'].'"> '.$row['prod_name'].' <a href="'.$row['website'].'">'.$row['website'].'</td></table> ';}} But i still want the result to fill up the field and when the field is full i want the next results on a row below this.... Anyone? Hi guys, I'm trying to query two tables for different data and echo the results that match both tables. Here are the tables I have and the query I'm trying to run. (Table) qc_reports (Fields) id report_date report_lot_number report_po report_supplier report_buyer report_inspectedby report_pulptemprange report_carrierconditions report_supplierclaim report_carrierclaim report_temprecorder report_temprange_N report_temprange_M report_temprange_B report_suppliercontact report_contactedby report_time report_comments (Table) qc_lots (Fields) id report_id lot_temprange lot_commodity lot_rpcs lot_brand lot_terms lot_cases lot_orgn lot_estnum lot_avgnum This is the query that I'm trying to do. Code: [Select] <?php $sql = "SELECT * FROM qc_reports, qc_lots WHERE "; if (!empty($start_date) and !empty($end_date)) $sql .= " qc_reports.report_date BETWEEN '$start_date' and '$end_date' AND "; if (!empty($search_fronteralot)) $sql .= " qc_reports.report_lot_number = '$search_fronteralot' AND "; if (!empty($search_buyer)) $sql .= " qc_reports.report_buyer = '$search_buyer' AND "; if (!empty($search_supplier)) $sql .= " qc_reports.report_supplier = '$search_supplier' AND "; if (!empty($search_po)) $sql .= " qc_reports.report_po = '$search_po' AND "; if (!empty($search_carrierconditions) and $search_carrierconditions != 'all') $sql .= " qc_reports.report_carrierconditions = '$search_carrierconditions' AND "; if (!empty($search_commodity) and $search_commodity != 'all') $sql .= " qc_lots.lot_commodity = '$search_commodity' AND "; if (!empty($search_inspectedby)) $sql .= " qc_reports.report_inspectedby = '$search_inspectedby' AND "; $sql = substr($sql, 0, -4); $query = mysql_query($sql); $numrows = mysql_num_rows($query); ?> RESULTS - <?php echo $numrows; ?> <hr> <table width='500'><tr><td><b>Date</b></td><td><b>Lot Number</b></td><td><b>PO</b></td><td> </td></tr> <tr> <td> </td> </tr> <?php while ($row = mysql_fetch_assoc($query)) { $id = stripslashes($row['id']); $report_lot_number = stripslashes($row['report_lot_number']); $report_po = stripslashes($row['report_po']); $report_date = stripslashes($row['report_date']); echo "<tr> <td>" . $report_date . "</td></td><td>" . $report_lot_number . "</td><td>" . $report_po . "</td><td><a href='view_report.php?id=" . $id . "'>View</a></td> </tr>"; } echo '</table><br><br><br><hr><br><br>'; } ?> All the variables are passed from a HTML form with $_POST. I need the search to work like this: If there is a value in a form field then the query gets appended with that value but when it gets to the $search_commodity it needs to search the second table (qc_lots) and check for the results. Any results that match have to be matched to the results from the first table (qc_reports) and display (echo) only qc_reports that match to both tables. The only common field is the report_id on the qc_lots table and the id on the qc_reports table. I'm stuck and need some guidance. Can someone help please? I have a table that contains the schools in my system: schools id name location ... Then, I have three tables that use the id of this table: schoolAdmins schoolID schoolContests schoolID students schoolID When I go to delete a school from my system, I want to check to see if that school is connected to any of these three other tables first. This is what I tried (but obviously failed because I'm here) where I'm passing the query the $studentID in question: SELECT * FROM schoolAdmins, schoolContests, students WHERE (schoolAdmins.schoolID = $schoolID) OR (schoolContests.schoolID = $schoolID) OR (students.schoolID = $schoolID) I'm really new to the concept of querying multiple tables in a single statement, so I'm just kind of guessing at this point. Thanks in advance. Below is a page which is supposed to output the name, blog contribution and picture of contributing members of a website. <div id="blog_content" class="" style="height:90%; width:97%; border:5px solid #c0c0c0; background-color: #FFFFFF;"> <!--opens blog content--> <?php //address error handling ini_set ('display_errors', 1); error_reporting (E_ALL & ~E_NOTICE); //include the config file require_once("config.php"); //Define the query. Select all rows from firstname column in members table, title column in blogs table,and entry column in blogs table, sorting in ascneding order by the title entry, knowing that the id column in mebers table is the same as the id column in blogs table. $sql = "SELECT blogs.title,blogs.entry,members.firstname,images.image FROM blogs LEFT JOIN members ON blogs.member_id = members.member_id LEFT JOIN images ON blogs.member_id = images.member_id ORDER BY blogs.title ASC "; $query = mysql_query($sql); if($query !== false && mysql_num_rows($query) > 0) { while(($row = mysql_fetch_assoc($query)) !== false) { echo '<div id="blog_content1" style="float:left; position:relative;bottom:18px;left:13px; background-color: #FFFFFF; height:16.7%; width:100%; border:0px none none;" <!--opens blog_content1 same as main center top 1 and 2 from index page everything scaled down by a factor of 3, heightwise--> <div class="red_bar" style="height:3%; width:100%; border:1px solid #959595;"> <!--a--> <div class="shade1" style="height:5px; width:100%; border:0px none none;"> </div> <div class="shade2" style="height:5px; width:100%; border:0px none none"> </div> <div class="shade3" style="height:5px%; width:100%; border:0px none none"> </div> </div> <!-- closes red bar--> <div class="content" style="height:28.3%; width:100%; border:0px none none;"> <!----> <div class="slideshow" id="keylin" style="float:left; width:20%; border:0px none none;"> <!--a--> <div><img header("Content-type: image/jpeg"); name="" alt="" id="" height="105" width="105" src="$row[image]" /></div> </div> <!-- closes pic--> <div class="content_text" style="float:right; position:relative;top:7px;left:0px; max-height:150px; width:78.5%; border-width:4.5px; border-bottom-style:solid; border-right-style:solid; border-color:#c0c0c0; "> <!--a-->'; echo "<h3>".$row['title']."</h3>"; echo "<p>" .$row['entry']."<br />".$row['firstname']."</p>"; echo '</div> <!-- closes content text--> </div> <!-- closes content--> </div> <!-- closes blog_content1-->'; } } else if($query == false) { echo "<p>Query was not successful because:<strong>".mysql_error()."</strong></p>"; echo "<p>The query being run was \"".$sql."\"</p>"; } else if($query !== false && mysql_num_rows($query) == 0) { echo "<p>The query returned 0 results.</p>"; } mysql_close(); //Close the database connection. ?> </div> <!-- closes blog content--> The select query is designed to retrieve all the blog contributions(represented by the fields blogs.title and blogs.entry) from the database, alongside the contributing member (member.firstname) and the member's picture(images.image), using the member_id column to join the 3 tables involved, and outputs them on the webpage. The title, entry and firstname values are successfully displayed on the resulting page. However, I can't seem to figure out how to get the picture to be displayed. Note that the picture was successfully stored in the database and I was able to view it on a separate page using a simple select query. It is now just a question of how to get it to display on this particularly crowded page. Anyone knows how I can output the picture in the img tag? I tried placing the header("Content-type: image/jpeg"); statement at the top of the php segment, then just right below the select query and finally just right above the img tag, but in every case, I just got a big white blank page starring at me. How and where should I place the header statement? And what else am I to do to get this picture displayed? Any help is appreciated. This topic has been moved to MySQL Help. http://www.phpfreaks.com/forums/index.php?topic=351561.0 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. 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 am trying to create a little code, and need to get info from more than one table, preferable like so: $query = "SELECT xxx FROM xxx WHERE xxx="xxx" .......... then do stuff here. $query = "SELECT yyy FROM yyy WHERE yyy="yyy" .......... how to do this the right way? I want to count the number of likes a status has. I have a 'likes' table seperate from the 'stream' table. For now I will keep it simple and only display the number of likes instead of displaying all the usernames that have liked it (even though the system already saves the usernames).
Here is the main query:
//query the database $query = mysql_query("SELECT `stream_id`, `stream_username`, `stream_status`, `stream_date` FROM stream ORDER BY stream_id DESC LIMIT 50"); //fetch the results / echo "<hr><br />"; WHILE($rows = mysql_fetch_array($query)): $name= $rows['stream_username']; $status = $rows['stream_status']; $statustime = $rows['stream_date']; $time = strtotime($statustime); $thisid = $rows['stream_id']; echo $name . $status . timeAgo($time) . " ago" echo "<br /><hr><br />"; endwhile;And here is one which counts the number of likes $likesresult = mysql_query("SELECT likes_location_id FROM likes WHERE likes_location_id='$thisid'"); $likescount = mysql_num_rows($likesresult);So how would I go about merging the two together and displaying '$likescount' after '$status'? Thanks, I have a MySQL data base with two tables. (questions then answers?) I need to query the second table to find related results from a query on the first table. Here is what I have, How do I get it to combine the two queries? <?php $db = @mysqli_connect('localhost', 'unsabook', 'Password', 'unsabook');
if (!$db) { $rs_message = $db->query ("SELECT * FROM `messages` ORDER BY `message_ID` ASC LIMIT 0,200");
while ( $message_array = $rs_message->fetch_assoc() )
echo '<div class="left10">';
$rs_replies = $db->query ("SELECT * FROM `replies` WHERE `message_ID` = '".$message_ID."' ORDER BY `reply_ID` DESC");
echo '<div class="left10">'; Hello to everybody! I'm developing a beach guid web site, and I need all beach spot have a Country, Region, SubRegion and Spot And I want display this in a <ul> hierarchy. I Create 4 tables TABLE coutry idCoutry name TABLE region idRegion idCoutry name TABLE subregion idSubRegion idRegion name TABLE beach idBeach idSubRegion name I would like display this values like that - Country - Region -SubRegion -Beach -Beach -Beach -Beach -Beach -SubRegion -Beach -Beach -Beach -Beach -Beach - Country - Region -SubRegion -Beach -Beach -Beach -Beach -Beach -SubRegion -Beach -Beach -Beach -Beach -Beach etc. I can do this with multi query but if I have 4000 Beachs the script made many query's If some can help me! Thanks! Hi everyone. I am a newbie to mysql php and have a question. I can display records in a loop from one table but i want to display the records from another table as well that has the same quoteid and where approved is "n" This is what have so far, but there is also only 3 fields from the quotes table i want to display in the loop. $sql = mysql_query("SELECT * FROM comments WHERE approved LIKE 'n%'"); while ($row = mysql_fetch_array($sql)) { $count = mysql_num_rows ( $sql ); foreach($row as $key=>$value){ $$key = htmlentities(stripslashes($value)); } echo ' Hope i make sense. this is the problem i have 2 tables table named ZAMJENE with fields id_event_1, id_event_2 table named EVENTS with fields id_event and event_date and i need echo $id_event_1 on date(EXTRACT FROM TABLE EVENTS FIELD event_date) can be replaced with $id_event_2 on date(EXTRACT FROM TABLE EVENTS FIELD event_date) structure must remains because i have half site build already and i have 3rd table where i just need use JOIN hope you understand problem Is it possible to do this with one query? Tried with union and join but no luck.
<?php $query = 'SELECT cashReward, pointsReward FROM pts WHERE signupsAvailable > 0 AND status = "active" AND id = :id'; $select = $db->prepare($query); $select->bindParam(':id', $id, PDO::PARAM_INT); $select->execute(); $rowCount = $select->rowCount(); $queryC = 'SELECT COUNT(id) FROM ignored_pts WHERE user = :username AND ptsId = :id'; $selectC = $db->prepare($queryC); $selectC->bindParam(':username', $userInfo['username'], PDO::PARAM_INT); $selectC->bindParam(':id', $id, PDO::PARAM_INT); $selectC->execute(); $count = $selectC->fetch(PDO::FETCH_COLUMN); if($rowCount == 1){// PTS // $row = $select->fetch(PDO::FETCH_ASSOC); if($count == 0){// IGNORED PTS // // ...................... // // INSERT INTO ignored_pts TABLE $row['cashReward'], $row['pointsReward']// // ...................... // print 'PTS IGNORED'; }else{ print 'You have already ignored this PTS!'; } }else{ print 'An invalid PTS was provided!'; } $db = NULL; ?> I am in the process of making a stream page where users can enter a status, like and comment others statuses, like on facebook.
I have 4 tables, stream, users, likes and comments as shown below, with the rows I will be using:
stream stream_status Hi all, I'm currently working on a webpage that dynamically shows tables of sales for products. I'm using SQL to store 2 tables, one containing product info (name, price etc.) and one containing sales for each product. One on of my web pages I want to show the product name and sales for a single year. This would involve ripping the product name from my product table and the sales and year from my sales table. Is this possible in one query? $Query= "select name from productTable and sales, year from salesTable where year = $desiredyear; Obviously I know this is wrong because it isn't working but am I even on the right lines? Thank you. Hi, I'm trying to write a query that retrieves the comments from one table where "touser=$username" and retrieve statuses from another table where byuser=$username, and I want to sort it all by date posted. How would I go about doing that? I'm used to writing normal single table queries, but retrieving from multiple tables is new to me. Thank you for your help in advance. 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! |