|
PHP - Select All Rows From Tables Joining Other Table But Select Only Rows That Match A Condition
I have 2 queries that I want to join together to make one row
Similar Tutorialshello i have a mysql table and i want to display each row in a different page with an added condition, the code: Code: [Select] if (isset($_GET["page"])) { $page = $_GET["page"]; } else { $page=1; }; $start_from = $page-1; $qry="SELECT * FROM ordering WHERE userid='$page' AND email='$email' LIMIT 0,1"; $result = mysql_query($qry); while($row = mysql_fetch_array($result)){ echo "<div dir='rtl' charset='utf8'><h2>"," hover cam: ",$row['hover_camera'], "<br> stills: ",$row['stills'], "<br> video_photographers: ",$row['video_photographers'], "<br> increase: ",$row['increase'], "<br> video_edit: ",$row['video_edit'], "<br> digital_album: ",$row['digital_album'], "<br> photo_album: ",$row['photo_album'], "<br> small_digital_album: ",$row['small_digital_album'], "<br> video_clip: ",$row['video_clip'], "<br> magnets: ",$row['magnets'], "<br> comments: ",$row['comments'], "<br> date: ",$row['date'], "<br><br></h2></div>"; } $sql = "SELECT COUNT(userid) FROM ordering"; $result = mysql_query($sql,$link); $row2 = mysql_fetch_row($result); $total_records = $row2[0]; $total_pages = $total_records ; for ($i=1; $i<=$total_pages; $i++) { echo "<a href='myorders.php?page=".$i."'>".$i."</a> "; }; the table is as so: Code: [Select] CREATE TABLE IF NOT EXISTS `ordering` ( `userid` int(11) unsigned NOT NULL AUTO_INCREMENT, `hover_camera` varchar(10) DEFAULT NULL, `stills` int(5) NOT NULL, `video_photographers` int(5) NOT NULL, `increase` int(5) NOT NULL, `video_edit` varchar(10) NOT NULL, `digital_album` varchar(10) DEFAULT NULL, `photo_album` varchar(10) DEFAULT NULL, `small_digital_album` varchar(20) DEFAULT NULL, `video_clip` varchar(10) DEFAULT NULL, `magnets` int(10) NOT NULL, `comments` text NOT NULL, `date` date NOT NULL, `fname` varchar(100) NOT NULL, `email` varchar(100) NOT NULL, PRIMARY KEY (`userid`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; now to the problem: this code displays all the userid as page numbers however, the email condition is good only for part of the rows it's a little hard to explain, i'll try an example: suppose the email '1@1.com' is at rows 22,25 and 30 my code displays 30 page numbers and page 22 is user 22, page 25 is user 25 etc. what i want is to display only the page numbers of the pages with the conditional email, and if possible to display them as 1,2,3 instead of 22,25,30 what Im basically trying to do is just like a phpmyadmin function... you select rows you want to update with a checkbox and then it takes you to a page where the rows that are clicked are shown in forms so that you can view and edit info in them... and then have 1 submit button to update them all at once. I have a cart script, and I need to display the products the user has added. This is a school assignment and I'm very stuck on this seemingly 'little' error. Right now, I have two tables: For time's sake, I am showing duplicate products with different prod_id's: Table: products prod_id category sub_category thumb_href image_href title desc summary manufacturer price 17 sofa leather red_leather_sofa_small.jpg red_leather_sofa_main.jpg Red Leather Sofa [BLOB - 134B] [BLOB - 59B] Balenty 999.99 18 sofa leather red_leather_sofa_small.jpg red_leather_sofa_main.jpg Red Leather Sofa [BLOB - 134B] [BLOB - 59B] Balenty 999.99 19 sofa leather red_leather_sofa_small.jpg red_leather_sofa_main.jpg Red Leather Sofa [BLOB - 134B] [BLOB - 59B] Balenty 999.99 And this is the cart table that holds temporary cart items (storing the product ID to pull later): Table: cart temp_cart_item cart_id (assigned by session_id() ) prod_id user_id 22 jier11u0e7cl2ghosjodpaark2 17 asdfasdf 23 jier11u0e7cl2ghosjodpaark2 17 asdfasdf 24 jier11u0e7cl2ghosjodpaark2 35 asdfasdf 25 jier11u0e7cl2ghosjodpaark2 5 asdfasdf 26 jier11u0e7cl2ghosjodpaark2 19 asdfasdf SO, user 'asdfasdf' is logged in and has selected 5 items for his cart - two of which are the same (prod_id 17). Basically, I need to compare the prod_id's of each table and output all products (and quantities) matching the temporary cart prod_id. What I have now to loop through and display the cart items are (bear with me): echo "<h1>Cart</h1>"; } // Display welcome and navigation links displayWelcome(); displayNav(); // Getting the prod_id's from the table cart to compare with the product table $cart_id = session_id(); $user_id = ($_SESSION['login_username']); $query = "SELECT prod_id FROM cart WHERE cart_id='$cart_id' AND user_id='$user_id'"; $result = mysqli_query($cxn,$query) or die("<h2 class=\"warning\">Whoa! Problem with the cart script here!</h2>"); $nrows = mysqli_num_rows($result); if($nrows < 1) { echo "<h2>Your cart is empty</h2><p><a href=\"catalog.php\">Go back to the catalog</a></p>"; } else { // Continue to gather products for all the product IDs in the products DB table... echo "<h4>$nrows items in your cart</h4>"; while($row = mysqli_fetch_array( $result )) { $user_id = ($_SESSION['login_username']); $product = $row['prod_id']; // Is the query my problem? $query = "SELECT * FROM products WHERE prod_id='$product'"; $result = mysqli_query($cxn,$query) or die("<h2 class=\"warning\">Whoa! Problem with the cart script here!</h2>"); $nrows = mysqli_num_rows($result); if($nrows < 1) { // If the item doesn't exist, display an error echo "<h2>That is not a valid product, or any of the items you had in your cart are missing or have been deleted.</h2><p><a href=\"catalog.php\">Go back to the catalog</a></p>"; // Protect from continually displaying an error by deleting the invalid cart record $deletequery = "DELETE FROM cart WHERE prod_id='$product'"; $deleteresult = mysqli_query($cxn,$deletequery) or die("<h2 class=\"warning\">Whoa! Problem with the cart script here!</h2>"); exit(); } while($row = mysqli_fetch_array( $result )) { // Then disply them in a table echo "<h2>Items in Your Cart:</h2>"; echo " <table id=\"products\"> <thead> <tr> <th class=\"col1 headercol\" scope=\"col\">Thumbnail</th> <th class=\"col2 headercol\" scope=\"col\">Summary</th> <th class=\"col3 headercol\" scope=\"col\">Manufacturer</th> <th class=\"col4 headercol\" scope=\"col\">Price</th> </tr> </thead> <tbody>\n"; $query = "SELECT SUM(price) FROM products WHERE prod_id='$product' GROUP BY price"; $priceResult = mysqli_query($cxn,$query) or die("<h2 class=\"warning\">Whoa! Problem with the cart script here!</h2>"); // keeps getting the next row until there are no more to get // Print out the contents of each row into a table echo "<tr> <td class=\"col1\">"; echo "<a class=\"imgHref\" href=\"detail.php?prod_id=".$row['prod_id']."\" title=\"".$row['title']."\"><img src=\"../images/".$row['thumb_href']."\" alt=\"".$row['title']."\" /></a>"; echo "</td>\n"; echo " <td class=\"col2\">"; echo "<h3 class=\"title\"><a href=\"detail.php?prod_id=".$row['prod_id']."\" title=\"".$row['title']."\">".$row['title']."</a> <span class=\"inlineh3\">by <a href=\"categories.php?manufacturer=".$row['manufacturer']."\" title=\"View all products by ".$row['manufacturer']."\">".$row['manufacturer']."</a></span></h3>"; echo "<p class=\"summary\">".$row['summary']."</p> <p class=\"descHeading\">&#9758; <em>Full Description:</em></p> <p class=\"desc\">".$row['desc']."</p>"; echo "</td>\n"; echo " <td class=\"col3\">"; echo "<p class=\"manufacturer\"><a href=\"categories.php?manufacturer=".$row['manufacturer']."\" title=\"View all products by ".$row['manufacturer']."\">".$row['manufacturer']."</a></p>"; echo "</td>\n"; echo " <td class=\"col4\">"; echo "<p class=\"price\">$".$row['price']."</p>"; echo "</td> </tr>\n"; } while($priceRow = mysqli_fetch_array( $priceResult )) { echo "<tr class=\"short\"><td class=\"col1\"><h3><a href=\"clear_cart.php\">Empty Cart</a></h3></td><td class=\"col2\"></td><td class=\"col3 nobackground\">Total:</td><td class=\"col4\">$".$priceRow[0]."</td></tr>"; } echo "</tbody> </table>"; echo "<p class=\"submitOrder\"><form action='submit_order.php' method='post' id='form'> <input type='submit' name='pButton' value='Submit Order'> <input name='order_total' type='hidden' id='order_total' value='".$priceRow[0]."' /> </form> </p>"; } } And so the cart.php looks something like this (this example asdfasdf has 6 items in his cart): So you can see that it only displays ONE item, even though asdfasdf DOES have 6 items in his cart (proven by checking database). And the price is not accurate, either. Can anyone help me in where my problem might be? Is it my query? Or my while clause? hello, im only getting the second half of my results showing in my dropdown box. here is my code. Code: [Select] if(isset($_GET['id'])) { $uid = $_GET['id']; } $forward = mysql_query("SELECT * FROM players WHERE status = '1' AND position = 'forward' ORDER BY name"); $forward1=""; while($row1 = mysql_fetch_array($forward)) { $forwardid=$row1["id"]; $forwardname=$row1["name"]; $forwardteam=$row1["team"]; $forward1.="<OPTION TITLE=\"$forwardteam\" VALUE=\"$forwardid\">".$forwardname.'</option>'; } $result = mysql_query("SELECT * FROM picks WHERE uid = '$uid' AND pickid = 'forward1'"); while($row = mysql_fetch_array($result)) { $playerid=$row['playerid']; } if(isset($forwardpick1)) { $result1 = mysql_query("SELECT * FROM players WHERE id = '$playerid'"); while($row1 = mysql_fetch_array($result1)) { $forwardpick1=$row1['name']; } $forwardpick1="<br /><br /><font color ='#000000' size = '5'><b>$forwardpick1"; } else { $forwardpick1=" <form method='POST' action='draftpick.php' name='forward1'> <p><font color='#000000'><select size='1' name='player'> <?php echo '$forward1'; ?> </select><br><br><input type='submit' value='Submit' name='submit'></font></p> </form> "; } any direction is appreciated. Hi, I have the project where i want to get the structure like tree. My table structu Section_Id Section_Parent Section_Name 1 0 America 2 0 China 3 1 New york 4 1 Washington 5 2 Buffalo How to retrieve the records.. I need a structure like this in a select button: America New york Buffalo Thanks,
$result = mysql_query("SELECT * FROM `friend_system` WHERE `friend_system_accepter` = '$myusername' ");
echo mysql_num_rows($result);
This displays the total rows in the table.
$result = mysql_query("SELECT COUNT(friend_system_accepter) FROM `friend_system` WHERE `friend_system_accepter` = '$myusername' ");
echo mysql_num_rows($result);
This displays '1', which is incorrect.
I want to echo out the number of rows where 'friend_system_accepter' = $myusername
Thanks
Hello, i am trying to get my form to list usernames ascending as an option, but the select field just returns blank. Here is my code: Code: [Select] /** * editUserForm - Displays the users database table in * a nicely formatted field table. */ function editUserForm(){ $q = "SELECT * " ."FROM ".TBL_USERS." ORDER BY username ASC"; $result = $database->query($q); while ($row = mysql_fetch_assoc($result)) { echo '<option value="'.$row["username"].'">'.$row["username"].'</option>'; } } Code: [Select] <form action="/" method="post"> <div class="column"> <p> <select name="user" id="user" placeholder="Select A User To Edit" class="{validate:{required:true}}"> <option>Select A User To Edit</option> <? editUserForm(); ?> </select> </p> <div class="action_bar"> <input type="submit" class="button blue" value="Submit Post" /> <a href="#modal" class="modal button">Cancel</a> </div> </form> All help is appreciated How can i select the last 3 rows in a table and order it by ASC. When i use DESC it displays the results in the wrong order. $query24 = mysql_query("SELECT * FROM notifications WHERE to_id='$session' AND state='1' ORDER BY id DESC LIMIT 3 "); So I have an array of IDs that I would only like to select from a database table instead of everything in the table. How might I go about doing that? Hello folks,
In trying to improve the user experience for my first WebApp I have decided to create two new tables - one a master file to contain a list of all stores, and the second a master file to contain a list of all products that are normally purchased - and I would like to use the values from these two tables as lookup values in dropdown listboxes, to which the user can also add new entries.
As it turns out, I'm stuck on the very first objective i.e. to lookup/pull-in the distinct values from the master tables.
The problem I'm having is that the query seems to return no rows at all...in spite of the fact that there a records in the table, and the exact same query (when run within the MySQL environment) returns all the rows correctly.
Is there something wrong with my code, or how can I debug to see whether or not the query is being executed?
Objective # 2, which is to allow new values to be entered into the dropdown listbox, and then inserted into the respective table is certainly waaay beyond my beginner skills, and I'll most certainly need to some help with that as well..so if I can get some code/directions in that regard it will be most appreciated.
Thank you.
<?php
$sql = "SELECT DISTINCT store_name FROM store_master ORDER BY store_name ASC";
$statement = $conn->prepare($sql);
$statement->execute();
$count = $statement->rowCount();
echo $count;
// fetch ALL results pertaining to the query
$result = $statement->fetchAll();
echo "<select name=\"store_name\">";
echo '<option value="">Please select...</option>';
foreach($result as $row)
{
echo "<option value='" . $row['store_name'] . "'></option>";
}
echo "</select>";
?>
I am building a commenting system, it's sort of like facebooks comments. I have it right now so it loads the first last 3 comments in the database, and then there's a button to view all comments. I have it working fine, except when you add another comment to it, and then click the view all comments, This is the script that loads onload showing the last 3 comments $limit = 3; $query5 = mysql_query("SELECT * FROM sub_comments WHERE status_id='$id' ORDER BY id DESC LIMIT $limit"); This is the php script that runs when you click view more //($start_id would be 3 in this case) $query5 = mysql_query("SELECT * FROM sub_comments WHERE status_id='$comment_id' ORDER BY id DESC LIMIT $start_id, 1000"); so to try to show you what's happening: OnLoad--------------------------------|---When you add more comments, (let's just say u add 1 comment before u click view comments)---- comment 9 --------------------------| comment 10 comment 8 --------------------------| comment 9 comment 7 --------------------------| comment 8 ------------------------------------------| comment 7 ----to be loaded onclick-------------|------What's loaded onclick--- comment 6 --------------------------| comment 7 comment 5 --------------------------| comment 6 comment 4 --------------------------| comment 5 comment 3 --------------------------| comment 4 comment 2 --------------------------| comment 3 comment 1 --------------------------| comment 2 ------------------------------------------| comment 1 (see how "comment 7" is being shown twice in a row) hirealimo.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 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.. I got problem in logic on my problem in select option. Here is my scenario. I have select option which has a compound list: example: P28 P30 P32 P33 P35 and I have tables which has a total count of compound that was already transact. for example I already transact 10 P28 compound. it will save in kanban_checker table field Total. and I have parameter_settings table which has the maximum and minimum per compound: fields----------data P28------------P28 P28_max-----10 P28_min------6 P30------------P28 P30_max-----10 P30_min------6 P32------------P28 P32_max-----10 P32_min------6 P33------------P28 P33_max-----10 P33_min------6 now the logic is: if P28 total = P28_max then notify that he already reach the maximum..it will not produce P28 because he already reach the maximum. if P28 total = P28_min then produce P28. same with other compound. Now, I got a problem in coding and logic, because in the select option I'm not the one who code it.. I just one who need to add the additional function.. here is the code for select option and the function that was created by other, Code: [Select] $smarty->assign('text_name1','Compound Code'); $smarty->assign('compoundIndexValue', array('0', 'P28', 'P30', 'P32', 'P33', 'P35', 'P35M', 'P38', 'P40', 'P41', 'P42', 'P43', 'P46', 'P47', 'PXX', 'P35W')); <tr> <td class="tdMainText">{$text_name1}</td> <td> <select class="tdMainElement" id="selCompound" onchange="isSelected('selCompound','txtLotCode')" onclick="clearNotify()" onblur="this.focus();" onkeypress="return false">{html_options values=$compoundIndexValue output=$names selected="0"} </select> </td> </tr> <script type="text/javascript"> function isSelected(obj1, obj2) { if (!document.getElementById(obj1).disabled) { document.getElementById(obj1).disabled = true; } if (document.getElementById('clear').disabled) { document.getElementById('clear').disabled = false; //enable it } if ((document.getElementById('chkDownGrade') != null)) { if (!document.getElementById('chkDownGrade').disabled) { document.getElementById('chkDownGrade').disabled = true; } } if (document.getElementById(obj2).disabled) { document.getElementById(obj2).disabled = false; } document.getElementById(obj2).focus(); document.getElementById(obj2).focus(); //notify("Gain Maximum Total"); } function disable() { if (!document.getElementById('clear').disabled) { document.getElementById('clear').disabled = true; } else { document.getElementById('clear').disabled = false; } } function clearNotify() { if (document.getElementById('notice') != null) { document.getElementsByTagName('body')[0].removeChild(document.getElementById('notice')) //>> clear the box } } </script> and here is the code that I tried to add: Code: [Select] $compound = mysql_real_escape_string($_POST['compound']); $sql = "SELECT P28, P28_max, P28_min, P30, P30_max, P30_min, P32, P32_max, P32_min, P33, P33_max, P33_min, P35, P35_max, P35_min, P35M, P35M_max, P35M_min, P35W, P35W_max, P35W_min, P38, P38_max, P38_min, P41, P41_max, P41_min, P42, P42_max, P42_min, P43, P43_max, P43_min, P46, P46_max, P46_min FROM parameter_settings"; $res_pcode = mysql_query($sql, $con); while($row = mysql_fetch_assoc($res_pcode)){ $P28 = $row['P28']; $P28_max = $row['P28_max']; $P28_min = $row['P28_min']; $P30 = $row['P30']; $P30_max = $row['P30_max']; $P30_min = $row['P30_min']; $P32 = $row['P32']; $P32_max = $row['P32_max']; $P32_min = $row['P32_min']; $P33 = $row['P33']; $P33_max = $row['P33_max']; $P33_min = $row['P33_min']; $P35 = $row['P35']; $P35_max = $row['P35_max']; $P35_min = $row['P35_min']; $P35M = $row['P35M']; $P35M_max = $row['P35M_max']; $P35M_min = $row['P35M_min']; $P35W = $row['P35W']; $P35W_max = $row['P35W_max']; $P35W_min = $row['P35W_min']; $P38 = $row['P38']; $P38_max = $row['P38_max']; $P38_min = $row['P38_min']; $P41 = $row['P41']; $P41_max = $row['P41_max']; $P41_min = $row['P41_min']; $P42 = $row['P42']; $P42_max = $row['P42_max']; $P42_min = $row['P42_min']; $P43 = $row['P43']; $P43_max = $row['P43_max']; $P43_min = $row['P43_min']; $P46 = $row['P46']; $P46_max = $row['P46_max']; $P46_min = $row['P46_min']; $P47 = $row['P47']; $P47_max = $row['P47_max']; $P47_min = $row['P47_min']; } // $sub_lotcode = substr($lotCode, 9, 4); $sql = "SELECT PCODE, total FROM kanba_checker WHERE PCODE = '$sub_lotcode' ORDER BY PCODE"; $res_kanban = mysql_query($sql, $con); $row_kanban = mysql_fetch_assoc($res_kanban); $PCODE = $row_kanban['PCODE']; $total = $row_kanban['total']; if($compound = $P28 || $total == $P28_max){ echo "<script type='text/javascript'>alert(P28 gain total maximum)</script>"; } else{ } but I don't know if my code and my logic is correct. I think I did to add function. But I don't know how.. I hope someone can help me or guide me to solve my problem. Thank you so much.. I'm trying to pull user referral data from my database. This is what I have so far. <table cellpadding="0" cellspacing="0" style="border:1px #000000 solid;" width="68%"> <tr> <td bgcolor="#eeeeee" style="padding:2px;border-right:1px #000 solid;border-bottom:1px #000 solid;"><font size="2" face="verdana"><b> User </b></font></td> <td bgcolor="#eeeeee" style="padding:2px;border-right:1px #000 solid;border-bottom:1px #000 solid;"><font size="2" face="verdana"><b> </b></font></td> <td bgcolor="#eeeeee" style="padding:2px;border-right:1px #000 solid;border-bottom:1px #000 solid;"><font size="2" face="verdana"><b> Date </b></font></td> <td bgcolor="#eeeeee" style="padding:2px;border-bottom:1px #000 solid;"><font size="2" face="verdana"><b> Visits </b></font></td> </tr> <? $lole=$_COOKIE["usNick"]; $tabla = mysql_query("SELECT * FROM tb_users where username='$lole' ORDER BY id ASC"); while ($row = mysql_fetch_array($tabla)) { echo "<tr><td><font size=\"2\" face=\"verdana\">"; echo $row["username"]; echo "</font></td><td><font size=\"2\" face=\"verdana\">"; echo $row["email"]; echo "</font></td><td><font size=\"2\" face=\"verdana\">"; echo $row["joindate"]; echo "</font></td><td><font size=\"2\" face=\"verdana\">"; echo $row["visits"]; echo "</font></td></tr>"; } echo "</table>"; ?> <script type="text/javascript"> initSortTable('myTable',Array('S','N','S','N','S')); </script> I just found out my user ID is missing from database.. Hi Everyone, I am working on developing a blog/comment system for my website. I have 3 tables, the first a blog post table with a row/primary key of id that is auto incremented as each new article is created via a form. The second table is a comments table with a row of article_id and the third table is a category table with an article_id row as well. From the main blog page I have links that will take users to each individual blog post using $_GET like so: Code: [Select] http://www.mydomain.com/blog/article/?id={$row['id']} From here I would like to be able to display the comment for that particular article below the article and also have a sidebar with links to blog categories which will then display each post according to whether they are in that category or not. My question is two-fold: First, what is the best way to pull the id field from the blog/post table, form, or url and enter it into the article_id rows of the second two tables? Should I use a hidden field, the URL id via $_GET, using a join, or what? Secondly: What is the best way to compare and display those rows so that the appropriate comments are being shown for the correct article, etc. This is my first time doing this so I am grateful for any help or suggestions. Thanks, kaiman Hi, So i'm having a little trouble trying to simplify some code I already have. Code: [Select] $result = mysql_query("SELECT images, cat_id FROM image_cat"); while ($row=mysql_fetch_array($result)) { $cat_id=$row['cat_id']; echo $cat_id; $imgresult = mysql_query("SELECT * FROM images WHERE cat_id='".$cat_id."'"); while ($col=mysql_fetch_array($imgresult)) { echo $col['img_id']; } } Okay, so this works, however i'm sure I can bring it together under one query, though i'm truly stumped as to how? I thought I could do a simple join: Code: [Select] $result = mysql_query("SELECT image_cat.cat_id, images.img_id WHERE image_cat.cat_id=images.image_cat"); This works, but repeats the image category on each loop. I could remove this via php if statements, but there must be a simpler/mysql solution. If it doesn't make any sense I need to get Img cat 1 -img 1 -img 2 img cat 2 -img 3 -img 4 etc etc That join will give img cat 1 -img 1 img cat 1 -img 2 img cat 2 -img 3 Hope that makes sense?? And any help would be amazing! Thanks. month a0 a1 a2 a3 a4 a5 a6 a7 a8 a9 a10 a11 a12 a13 a14 a15 a16 a17 a18 a19 a20 a21 a22 a23 a24 a25 a26 a27 a28 a29 a30 a31 1 puudu 01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 [20] [21] 22 23 24 25 [26] 27 28 29 30 31 2 puudu 01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 3 puudu 01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 4 puudu 01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 5 puudu 01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 6 puudu 01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 7 puudu 01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 8 puudu 01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 9 puudu 01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 10 puudu 01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 11 puudu 01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 12 puudu 01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 ; i need help .. with calculating how many days are between example: january = 1 ; day =23 to feb =2; day 23 in php ... how can i do that hello all, i want to display each row from mysql table in a different page using this code: Code: [Select] <? require_once('config.php'); //Connect to mysql server $link = mysql_connect(DB_HOST, DB_USER, DB_PASSWORD); if(!$link) { die("can't connect: " . mysql_error()); } $db = mysql_select_db(DB_DATABASE); if(!$db) { die("can't select database"); } mysql_query("SET NAMES 'hebrew'"); //mysql_set_charset('utf8',$link); if (isset($_GET["page"])) { $page = $_GET["page"]; } else { $page=1; }; $start_from = $page-1; $qry='SELECT * FROM ordering limit 0, 1'; $result = mysql_query($qry); while($row = mysql_fetch_array($result)){ echo "<div dir='rtl' charset='utf8'><h2>"," hover cam: ",$row['hover_camera'], "<br> stills: ",$row['stills'], "<br> video_photographers: ",$row['video_photographers'], "<br> increase: ",$row['increase'], "<br> video_edit: ",$row['video_edit'], "<br> digital_album: ",$row['digital_album'], "<br> photo_album: ",$row['photo_album'], "<br> small_digital_album: ",$row['small_digital_album'], "<br> video_clip: ",$row['video_clip'], "<br> magnets: ",$row['magnets'], "<br> comments: ",$row['comments'], "<br> date: ",$row['date'], "<br><br></h2></div>"; } $sql = "SELECT COUNT(userid) FROM ordering"; $result = mysql_query($sql,$link); $row2 = mysql_fetch_row($result); $total_records = $row2[0]; $total_pages = $total_records ; for ($i=1; $i<=$total_pages; $i++) { echo "<a href='table.php?page=".$i."'>".$i."</a> "; }; ?> this is the table: Code: [Select] CREATE TABLE IF NOT EXISTS `ordering` ( `userid` int(11) unsigned NOT NULL AUTO_INCREMENT, `hover_camera` varchar(10) DEFAULT NULL, `stills` int(5) NOT NULL, `video_photographers` int(5) NOT NULL, `increase` int(5) NOT NULL, `video_edit` varchar(10) NOT NULL, `digital_album` varchar(10) DEFAULT NULL, `photo_album` varchar(10) DEFAULT NULL, `small_digital_album` varchar(20) DEFAULT NULL, `video_clip` varchar(10) DEFAULT NULL, `magnets` int(10) NOT NULL, `comments` text NOT NULL, `date` date NOT NULL, PRIMARY KEY (`userid`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 ; the problem is that only the first row is displayed no matter which page i choose and i want each page to display the row assigned to it (page 15 = row 15 etc...) any suggestions? 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?? } ?> |
|||||||