|
PHP - Displaying Image From A Multiple Tables Select Query.
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. Similar Tutorialshirealimo.com.au/code1.php this works as i want it: Quote SELECT * FROM price INNER JOIN vehicle USING (vehicleID) WHERE vehicle.passengers >= 1 AND price.townID = 1 AND price.eventID = 1 but apparelty selecting * is not a good thing???? but if I do this: Quote SELECT priceID, price FROM price INNER JOIN vehicle....etc it works but i lose the info from the vehicle table. but how do i make this work: Quote SELECT priceID, price, type, description, passengers FROM price INNER JOIN vehicle....etc so that i am specifiying which colums from which tables to query?? thanks 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! I want to select the user from super_administrators, administrators, teachers and students, and give the user permission based from what table he "came". But is giving the "Query failed" error... Code: [Select] <?php //Start session session_start(); //Include database connection details require_once('../config/config.php'); //Array to store validation errors $errmsg_arr = array(); //Validation error flag $errflag = false; //Connect to mysql server $link = mysql_connect(DB_HOST, DB_USER, DB_PASSWORD); if(!$link) { die('Failed to connect to server: ' . mysql_error()); } //Select database $db = mysql_select_db(DB_DATABASE); if(!$db) { die("Unable to select database"); } //Function to sanitize values received from the form. Prevents SQL injection function clean($str) { $str = @trim($str); if(get_magic_quotes_gpc()) { $str = stripslashes($str); } return mysql_real_escape_string($str); } //Sanitize the POST values $email = clean($_POST['email']); $password = clean($_POST['password']); //Input Validations if($email == '') { $errmsg_arr[] = 'O campo Email nao foi preenchido.'; $errflag = true; } if($password == '') { $errmsg_arr[] = 'O campo Senha nao foi preenchido.'; $errflag = true; } //If there are input validations, redirect back to the login form if($errflag) { $_SESSION['ERRMSG_ARR'] = $errmsg_arr; session_write_close(); header("location: ../index.php"); exit(); } //Create query $qry = "SELECT * FROM super_administrators,administrators,teachers,students WHERE email = '$email' AND passwd = '".md5($_POST['password'])."'"; $result = mysql_query($qry); $member = mysql_fetch_assoc($result); $table = mysql_field_table('$result', '0'); //Check whether the query was successful or not if($result) { if(mysql_num_rows($result) == 1) { if($table == 'super_administrators') { session_regenerate_id(); $_SESSION['SESS_ID'] = $member['id']; $_SESSION['SESS_NAME'] = $member['name']; $_SESSION['SESS_EMAIL'] = $member['email']; session_write_close(); header("location: ../users/sadmin/index.php"); exit(); } if($table == 'administrators') { session_regenerate_id(); $_SESSION['SESS_ID'] = $member['id']; $_SESSION['SESS_SCHOOL_ID'] = $member['school_id']; $_SESSION['SESS_NAME'] = $member['name']; $_SESSION['SESS_EMAIL'] = $member['email']; session_write_close(); header("location: ../users/admin/index.php"); exit(); } if($table == 'teachers') { session_regenerate_id(); $_SESSION['SESS_ID'] = $member['id']; $_SESSION['SESS_SCHOOL_ID'] = $member['school_id']; $_SESSION['SESS_NAME'] = $member['name']; $_SESSION['SESS_EMAIL'] = $member['email']; session_write_close(); header("location: ../users/prof/index.php"); exit(); } if($table == 'students') { session_regenerate_id(); $_SESSION['SESS_ID'] = $member['id']; $_SESSION['SESS_SCHOOL_ID'] = $member['school_id']; $_SESSION['SESS_CLASS_ID'] = $member['class_id']; $_SESSION['SESS_NAME'] = $member['name']; $_SESSION['SESS_REGISTRATION'] = $member['registration']; $_SESSION['SESS_EMAIL'] = $member['email']; session_write_close(); header("location: ../users/aluno/index.php"); exit(); } } else { $errmsg_arr[] = 'Suas informacoes de login estao incorreta. Por favor, tente novamente.'; $errflag = true; $_SESSION['ERRMSG_ARR'] = $errmsg_arr; session_write_close(); header("location: ../index.php"); exit(); } } else { die("Query failed"); } ?> $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> 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? Hey there. Not sure how to word this. I've been flying blind as a newbie trying to figure out some pagination for a left joined query. I've got syntax errors trying to set up the SELECT COUNT function that adds up the results of the search on a previous page so it knows how many results matched both tables. Right now, I've got this mess, and it's giving me a syntax error, "You have an error in your SQL syntax; .... near 'LEFT JOIN plantae ON (descriptors.plant_id = plantae.pla' at line 12" Code: [Select] $data = "Select (SELECT COUNT(*) FROM descriptors ) AS count1, (SELECT COUNT(*) FROM plantae ) AS count2 LEFT JOIN plantae ON (descriptors.plant_id = plantae.plant_name) WHERE `leaf_shape` LIKE '%$select1%' AND `leaf_venation` LIKE '%$select3%' AND `leaf_margin` LIKE '%$select4%'"; $result = mysql_query ($data); if (!$result) { die("Oops, my query failed. The query is: <br>$data<br>The error is:<br>".mysql_error()); } I have a photo album style gallery to build and i'm finding it dificult to list all the table names (these are names of photo albums) and then enter the data into a seperate query for each album name (these will change often so i cant keep updating the file as normal. this will then post all the data to the xml file and show the set of photos in the individual albums in a flash file. can anyone help me where im going wrong at all? <?php $dbname = 'cablard'; if (!mysql_connect('localhost', 'cablard', '')) { echo 'Could not connect to mysql'; exit; } $sql = "SHOW TABLES FROM $dbname"; $result = mysql_query($sql); if (!$result) { echo "DB Error, could not list tables\n"; echo 'MySQL Error: ' . mysql_error(); exit; } while ($row = mysql_fetch_row($result)) { echo "Table: {$row[0]}\n"; } mysql_free_result($result); $query = "SELECT * FROM photo ORDER BY id DESC"; $result2 = mysql_query ($query) or die ("Error in query: $query. ".mysql_error()); while ($row = mysql_fetch_array($result2)) { echo " <image> <date>".$row['date']."</date> <title>".$row['title']."</title> <desc>".$row['description']."</desc> <thumb>".$row['thumb']."</thumb> <img>".$row['image']."</img> </image> "; } ?> Thanks James Hi,
How can I select multiple columns from two tables and run a search through multiple fields?
My tables a
t_persons (holds information about persons)
t_incidents (holds foreign keys from other tables including t_persons table)
What I want is to pull some columns from the two tables above and run a search with a LIKE criteria, something like below. The code originally worked well with only one table, but for two tables it generate errors:
$query = "SELECT p.PersonID ,p.ImagePath ,p.FamilyName ,p.FirstName ,p.OtherNames ,p.Gender ,p.CountryID ,i.IncidentDate ,i.KeywordID ,i.IncidentCountryID ,i.StatusID FROM t_incidents AS i LEFT JOIN t_persons AS p ON i.PersonID = p.PersonID WHERE FamilyName LIKE '%" . $likes . "%' AND FirstName LIKE '%" . $likes . "%' AND OtherNames LIKE '%" . $likes . "%' AND Gender LIKE '%" . $likes . "%' AND IncidentDate LIKE '%" . $likes . "%' AND KeywordID LIKE '%" . $likes . "%' AND IncidentCountryID LIKE '%" . $likes . "%' AND StatusID LIKE '%" . $likes . "%' ORDER BY PersonID DESC $pages->limit";Errors a Column 'IncidentDate' in where clause is ambiguous Column 'KeywordID' in where clause is ambiguous Column 'IncidentCountryID' in where clause is ambiguous Column 'StatusID' in where clause is ambiguousThese columns are foreign keys on t_incidents table. I have also attached the table relationship diagram if it helps. I will appreciate any better way to do this. Thanx. Joseph Attached Files Model - 3.png 76.6KB 0 downloads This topic has been moved to MySQL Help. http://www.phpfreaks.com/forums/index.php?topic=348013.0 Ok so I am pretty good with mysql and performing queries to get the data that I need. However, I have a question for any of you gurus out there that may be able to help me with an issue that I always run into with certain types of queries. Say I have 2 tables like this: Galleries id name 1 My Photos Gallery Photos id gallery_id photo 1 1 photo1.jpg 2 1 photo2.jpg 3 1 photo3.jpg Now usually when I pull this info from the database I have to do 2 separate queries in order to get the data and then link them so I would do something like. <?php //make the gallery query $query = "SELECT * FROM galleries"; $results = mysql_query($query); //setup an array for the galleries data $gallery_data = array(); //loop through the galleries for($i=0;$i<mysql_num_rows($results);$i++){ //add the gallery info to the gallery data array $gallery_data[$i] = mysql_fetch_array($results); //make the photos query with the gallery id $photo_query = "SELECT * FROM gallery_photos WHERE gallery_id='".mysql_real_escape_string($gallery_data[$i]['id'])."'"; $photo_results = mysql_query($photo_query); //setup the photos array $photo_data = array(); //put the photos in the array for($n=0;$n<mysql_num_rows($photo_results);$n++){ $photo_data[$n] = mysql_fetch_array($photo_results); } //add the photos to the gallery array $gallery_data[$i]['photos'] = $photo_data; } //now to display it is like this if(is_array($gallery_data)){ foreach($gallery_data as $gallery){ echo $gallery['name']; //show the photos if(is_array($gallery['photos'])){ foreach($gallery['photos'] as $photo){ echo $photo['photo']; } } } } ?> So my question is there a way to get all of this data at one time. I know how to do multiple queries in one and to do joins but they can only return one row as far as I know of. The only other way that I know how to do this is by ordering them by name and selecting them directly from the photos table and then just getting the gallery name like this: SELECT *,(SELECT name FROM galleries WHERE id=gallery_photos.id) AS gallery_name FROM gallery_photos ORDER BY gallery_id Then I could just do one loop and see if the name has changed and if so to output the new name. But I would like to know if there is a way to get a second set of results as an array from a query so I could just select the galleries and photos all in one query. Any help is appreciated. 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. I need some help with the query for a form that selects values across multiple columns, and allow users to select multiple values in several columns. http://brinsterinc.com/tpa/tpasearch.php I assume you build the where clause for the sql by determining if there is an option value has been selected in the single-value select boxes. But how do I handle the multi-select list boxes? I'm desperate for help! Need to get somewhere with this over the weekend! TIA! Hello guys, I'm trying to figure out how to display and image that is located in one table (profile) and make it correspond to a variable on another table (latest_post) to where you echo out the variable from the table(latest_post) and the image from table(profile) appears next to the post. any how here is my code: Code: [Select] <?php $query = mysql_fetch_array(mysql_query("SELECT person FROM latest_post AND avatar_img FROM profile WHERE id FROM latest_post = id FROM profile ")); echo $query['avatar_img']; ?> Also when echoing out the query I only get the name of the image and not the image this is most likely because my image is stored in a different folder right? Thanks in advance, your help is much appreciated since I'm barely a beginner at php and MySQL. I wrote the page below to display a member's area of a website. The page is a mix of html and php code. the first part of is a block of php, which authenticates the user and displays the header. The middle part is a block of html which contains the bulk of the page. Embedded in this block of html are two sections of php, the first of which is supposed to display a user uploaded picture, enclosed in div tags, and the second of which, prints out the user's first name, from the database. The end of the script is a line of php that displays the footer. Now the page displays correctly when the section of php that contains the select query for displaying the picture(starting line 104) is omitted. But once I include that section, all i see is a blank page. Why is that section of php problematic? What can be done to fix it? Here is the data for the full page. Thanks for any help. <?php //address error handling ini_set ('display_errors', 1); error_reporting (E_ALL & ~E_NOTICE); //authenticate user require('auth.php'); //define title define('TITLE' , 'Members'); require ('header.html'); //need the header ?> <div id="main" style="background-color: #FFFFFF; height:71%; width:101%; border:0px none none; margin:auto; "> <!-- --> <div id="main_left" style="float:left; height:100%; width:20%; border:0px none none;"> <!--opens main left--> <div id="main_left_top" style="float:left; position:relative;bottom:5px;right:5px; height:31.25%; width:100%; background-color: #FFFFFF; border:1px solid #c0c0c0; margin:1px;"> <!--opens main left top--> </div> <!-- closes main left top--> <div id="main_left_center" style="float:left; background-color: #FFFFFF; height:33%; width:100%; border-color:#a0a0a0;border-style:outset;border-width:1px; margin:auto; "> <!--opens the white content area--> </div> <!-- closes main left center--> <div id="main_left_bottom" style="float:left; background-color: #FFFFFF; height:33%; width:100%; border-color:#a0a0a0;border-style:outset;border-width:1px; margin:auto; "> <!--opens the white content area--> </div> <!-- closes main left bottom--> </div> <!-- closes main left--> <div id="main_center" class="content_text" style="float:left; height:100%; width:58%; background-color: #FFFFFF; border:1px solid #c0c0c0;"> <!--opens main center--> <div id="image_box" style="float:left; background-color: #c0c0c0; height:150px; width:140px; border-color:#a0a0a0;border-style:outset;border-width:1px; margin:auto; "> <?php //address error handling ini_set ('display_errors', 1); error_reporting (E_ALL & ~E_NOTICE); $query = "SELECT* FROM images WHERE member_id ='{$_SESSION['id']}' AND cartegoty 'main' "; $result = mysql_query($query); $result_data = mysql_fetch_array($result); header("Content-type: image/jpeg") ; echo $result_data['image']; ?> </div> <a href="upload_image_page.php">click here to uplaod a picture</a> <h1>Welcome <?php echo ucfirst($_SESSION['firstname']);?></h1> <a href="member_profile.php">My Profile</a> | <a href="logout.php">Logout</a> <p>This is a password protected area only accessible to members. </p> <a href="blog_entries.php">Add to Hahap Tok Library</a> </div> <!-- closes main center--> <div id="main_right" style="float:left; background-color: #FFFFFF; height:100%; width:20%; border-color:#a0a0a0;border-style:outset;border-width:1px; margin:auto; "> <!--opens the white content area--> <div id="main_right_top" style="float:left; background-color: #FFFFFF; height:33%; width:100%; border-color:#a0a0a0;border-style:outset;border-width:1px; margin:auto; "> <!--opens the white content area--> </div> <!-- closes main left top--> <div id="main_right_center" style="float:left; background-color: #FFFFFF; height:33%; width:100%; border-color:#a0a0a0;border-style:outset;border-width:1px; margin:auto; "> <!--opens the white content area--> </div> <!-- closes main left center--> <div id="main_right_bottom" style="float:left; background-color: #FFFFFF; height:34%; width:100%; border-color:#a0a0a0;border-style:outset;border-width:1px; margin:auto; "> <!--opens the white content area--> </div> <!-- closes main left bottom--> </div> <!-- closes main right--> </div> <!-- closes main--> <?php require('footer.html'); ?> Im doing a search system and Im having some problems.
I need to search in two tables (news and pages), I already had sucess doing my search system for just one table, but for two tables its not easy to do.
I already use a select statment with two tables using UNION because I want to show number of search results, that is number of returned rows of my first sql statment.
But now I need to do a select statment that allows me to acess all fields of my news table and all fields of my pages table.
I need to acess in my news table this fields: id, title, content, link, date, nViews
I need to acess in my pages table this fields: id, title, content, link
Im trying to do this also with UNION, but in this case Im not having any row returning.
Do you see what I have wrong in my code?
<?php //first I get my $search keyword $search = $url[1]; $pdo = connecting(); //then I want to show number of returned rows for keyword searched $readALL = $pdo->prepare("SELECT title,content FROM news WHERE title LIKE ? OR content LIKE ? UNION SELECT title,content FROM pages WHERE title LIKE ? OR content like ?"); $readALL->bindValue(1,"%$search%", PDO::PARAM_STR); $readALL->bindValue(2,"%$search%", PDO::PARAM_STR); $readALL->bindValue(3,"%$search%", PDO::PARAM_STR); $readALL->bindValue(4,"%$search%", PDO::PARAM_STR); $readALL->execute(); //I show number of returned rows echo '<p>Your search keyword returned <strong>'.$readALL->rowCount().'</strong> results!</p>'; //If dont return any rows I show a error message if($readALL->rowCount() <=0){ echo 'Sorry but we didnt found any result for your keyword search.'; } else{ //If return rows I want to show, if it is a page result I want to show title and link that I have in my page table //if it is a news result I want to show title and link that I have in my news table and also date of news echo '<ul class="searchlist">'; $readALL2 = $pdo->prepare("SELECT * FROM news WHERE status = ? AND title LIKE ? OR content LIKE ? LIMIT 0,4 UNION SELECT * FROM pages where title LIKE ? OR content LIKE ? LIMIT 0,4"); $readALL2->bindValue(1, '1'); $readALL2->bindValue(2, "%$search%", PDO::PARAM_STR); $readALL2->bindValue(3, "%$search%", PDO::PARAM_STR); $readALL2->bindValue(4, "%$search%", PDO::PARAM_STR); $readALL2->execute(); while ($result = $readALL2->fetch(PDO::FETCH_ASSOC)){ echo '<li>'; echo '<img src="'.BASE.'/uploads/news/'.$result['thumb'].'"/>'; echo '<a href="'.BASE.'/news/'.$result['id_news'].'">'.$result['title'].'</a>'; //if it is a news result I also want to show date on my list //echo '<span id="date">'.$result['date'].'</span>'; echo '</li>'; } echo ' </ul>'; //but how can I do my select statement to have access to my news table fields and my page table fields?? } ?> Hello all,
Based on the suggestion of you wonderful folks here, I went away for a few days (to learn about PDO and Prepared Statements) in order to replace the MySQLi commands in my code. That's gone pretty well thus far...with me having learnt and successfully replaced most of my "bad" code with elegant, SQL-Injection-proof code (or so I hope).
The one-and-only problem I'm having (for now at least) is that I'm having trouble understanding how to execute an UPDATE query within the resultset of a SELECT query (using PDO and prepared statements, of course).
Let me explain (my scenario), and since a picture speaks a thousand words I've also inlcuded a screenshot to show you guys my setup:
In my table I have two columns (which are essentially flags i.e. Y/N), one for "items alreay purchased" and the other for "items to be purchased later". The first flag, if/when set ON (Y) will highlight row(s) in red...and the second flag will highlight row(s) in blue (when set ON).
I initially had four buttons, two each for setting the flags/columns to "Y", and another two to reverse the columns/flags to "N". That was when I had my delete functionality as a separate operation on a separate tab/list item, and that was fine.
Now that I've realized I can include both operations (update and delete) on just the one tab, I've also figured it would be better to pare down those four buttons (into just two), and set them up as a toggle feature i.e. if the value is currently "Y" then the button will set it to "N", and vice versa.
So, looking at my attached picture, if a person selects (using the checkboxes) the first four rows and clicks the first button (labeled "Toggle selected items as Purchased/Not Purchased") then the following must happen:
1. The purchased_flag for rows # 2 and 4 must be switched OFF (set to N)...so they will no longer be highlighted in red.
2. The purchased_flag for row # 3 must be switched ON (set to Y)...so that row will now be highlighted in red.
3. Nothing must be done to rows # 1 and 5 since: a) row 5 was not selected/checked to begin with, and b) row # 1 has its purchase_later_flag set ON (to Y), so it must be skipped over.
Looking at my code below, I'm guessing (and here's where I need the help) that there's something wrong in the code within the section that says "/*** loop through the results/collection of checked items ***/". I've probably made it more complex than it should be, and that's due to the fact that I have no idea what I'm doing (or rather, how I should be doing it), and this has driven me insane for the last 2 days...which prompted me to "throw in the towel" and seek the help of you very helpful and intellegent folks. BTW, I am a newbie at this, so if I could be provided the exact code, that would be most wonderful, and much highly appreciated.
Thanks to you folks, I'm feeling real good (with a great sense of achievement) after having come here and got the great advice to learn PDO and prepared statements.
Just this one nasty little hurdle is stopping me from getting to "end-of-job" on my very first WebApp. BTW, sorry about the long post...this is the best/only way I could clearly explaing my situation.
Cheers guys!
case "update-delete":
if(isset($_POST['highlight-purchased']))
{
// ****** Setup customized query to obtain only items that are checked ******
$sql = "SELECT * FROM shoplist WHERE";
for($i=0; $i < count($_POST['checkboxes']); $i++)
{
$sql=$sql . " idnumber=" . $_POST['checkboxes'][$i] . " or";
}
$sql= rtrim($sql, "or");
$statement = $conn->prepare($sql);
$statement->execute();
// *** fetch results for all checked items (1st query) *** //
$result = $statement->fetchAll();
$statement->closeCursor();
// Setup query that will change the purchased flag to "N", if it's currently set to "Y"
$sqlSetToN = "UPDATE shoplist SET purchased = 'N' WHERE purchased = 'Y'";
// Setup query that will change the purchased flag to "Y", if it's currently set to "N", "", or NULL
$sqlSetToY = "UPDATE shoplist SET purchased = 'Y' WHERE purchased = 'N' OR purchased = '' OR purchased IS NULL";
$statementSetToN = $conn->prepare($sqlSetToN);
$statementSetToY = $conn->prepare($sqlSetToY);
/*** loop through the results/collection of checked items ***/
foreach($result as $row)
{
if ($row["purchased"] != "Y")
{
// *** fetch one row at a time pertaining to the 2nd query *** //
$resultSetToY = $statementSetToY->fetch();
foreach($resultSetToY as $row)
{
$statementSetToY->execute();
}
}
else
{
// *** fetch one row at a time pertaining to the 2nd query *** //
$resultSetToN = $statementSetToN->fetch();
foreach($resultSetToN as $row)
{
$statementSetToN->execute();
}
}
}
break;
}
CRUD Queston.png 20.68KB
0 downloads
|
|||||||