|
PHP - Select Counting Of Two Tables To Paginate Their Query Results
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()); } Similar TutorialsHello 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
Hoping someone can help me. I am trying to limit the number of results from a sql database and I assume that I should use pagination. How do I do this with the following code? Thanks in advance. Code: [Select] <?php if ($_GET[search] == 1) $qstring = "WHERE TITLE LIKE '%$_POST[search]%'"; if ($_GET[search] == 2) { $qstring = "WHERE CATEGORY LIKE '%-$_POST[search_cat]-%'"; $SQL3 = "SELECT * from CATEGORIES WHERE ID = '%-$_POST[search_cat]-%'"; $result3 = @mysql_query( $SQL3 ); $row3 = @mysql_fetch_array( $result3 ); } if ($_GET[search] == 3) { $qstring = "WHERE PACKAGE = '$_POST[search_package]'"; } $setpage = 'add_listings'; $return = 'manage_listings'; $table = "LISTINGS"; if (empty($qstring)) $qstring = 'ORDER BY TITLE ASC'; $qstring = "$qstring"; ?> <?php if ($rowxxx[HELPBOX] == $nil || $rowxxx[HELPBOX] == Show) { ?> <table width="100%" cellpadding="0" cellspacing="0" border="0"> <tr> <td class="td3" colspan="2"><b> Tool Tips</b></td> </tr> <tr> <td class="td4" valign="top"> <p><img src="../images/Help.png" width="64" height="64"></p> </td> <td class="td4" width="100%" valign="top"> <table width="100%" align="center" cellpadding="0" cellspacing="0" border="0"> <tr> <td> <div align="center"><img src="../images/edit_listing.jpg" border="0" alt="Edit this listing" width="22" height="22" /></div> </td> <td class="font1" width="100%"> Edit this listing.</td> </tr> <tr> <td> <div align="center"><img src="../images/delete-page-red.gif" alt="Delete this listing" border="0" width="14" height="14" /></div> </td> <td class="font1" width="100%"> Delete this listing.</td> </tr> <tr> <td> <div align="center"><img src="../images/user-group3.gif" width="14" height="14"></div> </td> <td class="font1" width="100%"> Duplicate this listing and edit its clone.</td> </tr> <tr> <td><a href="?page=add_photos&id=<?php echo $row[ID]; ?>"><img src="../images/Iphoto.png" width="22" height="22" border="0"></a></td> <td class="font1" width="100%"> Add photos.</td> </tr> <tr> <td> </td> <td class="font1" width="100%"> <?php $sql_active_accounts = "SELECT count(*) FROM LISTINGS"; $res_active_accounts = @mysql_query($sql_active_accounts); $row_active_accounts = @mysql_fetch_row($res_active_accounts); ?> <b> ( <?php echo " $row_active_accounts[0] "; if ($row_active_accounts == $nil) echo " 0 "; ?> ) </b>Listings in your database.</td> </tr> </table> </td> </tr> </table> <br> <?PHP } ?> <form action="?page=<?php echo $_GET[page]; ?>&search=2" method="post" name="form1" id="form1"> <table width="100%" cellpadding="0" cellspacing="0" border="0"> <tr> <td class="td3"> <b>Find Listings by Category</b></td> </tr> </table> <table width="100%" cellpadding="0" cellspacing="0" border="0"> <tr> <td class="td4" nowrap width="100%"> Select Category</td> <td class="td4"> <select name="search_cat"> <option selected> <?PHP ECHO $row3[TITLE]; ?> </option> <?PHP $SQLcaT = "SELECT * from CATEGORIES ORDER BY TITLE ASC"; $resultcaT = @mysql_query( $SQLcaT ); while( $rowcaT = @mysql_fetch_array( $resultcaT ) ) { $sql2 = "SELECT count(*) FROM LISTINGS WHERE CATEGORY LIKE '%-$rowcaT[ID]-%'"; $res2 = @mysql_query($sql2); $row2 = @mysql_fetch_row($res2); ?> <option value = "<?php echo $rowcaT[ID]; ?>"> <?php echo "$rowcaT[TITLE]"; if (empty($row2[0])) echo ' [ 0 ]'; else echo " [ $row2[0] ]"; ?> </option> <?PHP } ?> </select> </td> <td class="td4"> <input type="submit" name="Submit" value="Search" /> </td> </tr> </table> </form> <br> <form action="?page=<?php echo $_GET[page]; ?>&search=3" method="post" name="form1" id="form1"> <table width="100%" cellpadding="0" cellspacing="0" border="0"> <tr> <td class="td3"> <b>Find Listings by Package</b></td> </tr> </table> <table width="100%" cellpadding="0" cellspacing="0" border="0"> <tr> <td class="td4" nowrap width="100%"> Select Package</td> <td class="td4"> <select name="search_package" id="search_package"> <option selected value ="<?php echo $row[DEFAULTPACKAGE]; ?>"> <?PHP $SQLlu = "SELECT * from PACKAGES WHERE ID = '$row[DEFAULTPACKAGE]'"; $resultlu = @mysql_query( $SQLlu ); $rowlu = @mysql_fetch_array( $resultlu ); ?> <?PHP echo $rowlu[NAME]; ?> </option> <?PHP $SQL3 = "SELECT * from PACKAGES ORDER BY NAME ASC"; $result3 = mysql_query( $SQL3 ); while( $row3 = mysql_fetch_array( $result3 ) ) { ?> <option value="<?php echo $row3[ID]; ?>"> <?php echo $row3[NAME]; ?> </option> <?PHP } ?> </select> </td> <td class="td4"> <input type="submit" name="Submit" value="Search" /> </td> </tr> </table> </form> <br> <form action="?page=<?php echo $_GET[page]; ?>&search=1" method="post" name="form1" id="form1"> <table width="100%" cellpadding="0" cellspacing="0" border="0"> <tr> <td class="td3"> <b>Find Listing by Name</b></td> </tr> </table> <table width="100%" cellpadding="0" cellspacing="0" border="0"> <tr> <td class="td4" nowrap width="100%"> Listing Title</td> <td class="td4"> <input type="text" name="search" value="<?php echo $_POST[search]; ?>" size="40" /> </td> <td class="td4"> <input type="submit" name="Submit" value="Search" /> </td> </tr> </table> </form> <br> <table width="100%" cellpadding="0" cellspacing="0" border="0"> <tr> <td class="td3"> <b>Last Listing Added</b></td> </tr> </table> <?php $SQL = "SELECT * from $table ORDER BY ID DESC LIMIT 1"; $result = @mysql_query( $SQL ); while( $row = @mysql_fetch_array( $result ) ) { $check = 1; ?> <table width="100%" cellpadding="0" cellspacing="0" border="0"> <tr> <?PHP $filename = "$svr_rootscript/product_images/thumb/$row[IMAGENAME].jpg"; if (file_exists($filename)) { ?> <td nowrap class="td4" align="center" valign="top"> <a href="?page=add_photos&id=<?php echo $row[ID]; ?>"><img src="../product_images/thumb/<?php echo "$row[IMAGENAME]"; ?>.jpg" border="1" /></a> </td> <?PHP } ?> <td class="td4" colspan="2" width="100%"><b> <?php echo "<a href = \"?page=$setpage&id=$row[ID]&qt=update\">$row[TITLE]</a>"; ?> </b><br> <span class="font3">Category(s): <?PHP $pieces = explode("-", $row[CATEGORY]); foreach ($pieces as $cats => $value) { if ($value != $nil && $value != '-' && $value != '--') { $SQLCat = "SELECT * from CATEGORIES WHERE ID = '$value'"; $resultCat = @mysql_query( $SQLCat ); $rowCat = @mysql_fetch_array( $resultCat ); echo "$rowCat[TITLE], "; } } ?> </span></td> <td nowrap class="td4"><a href="?page=add_photos&id=<?php echo $row[ID]; ?>"><img src="../images/Iphoto.png" width="22" height="22" border="0"></a></td> <td class="td4"><a href="<?php echo "?page=$setpage&id=$row[ID]&qt=insert&clone=1"; ?>"> <img src="../images/user-group3.gif" border="0" alt="Edit this listing" width="14" height="14" /></a> </td> <td class="td4"><a href="<?php echo "?page=$setpage&id=$row[ID]&qt=update"; ?>"> <img src="../images/edit-comment-orange.gif" border="0" alt="Edit this listing" width="14" height="14" /></a> </td> <td class="td4"><a href="../library/delete.php?id=<?php echo $row[ID]; ?>&return=<?php echo $return; ?>&type=<?php echo "$table"; ?>"> <img src="../images/delete-page-red.gif" alt="Delete this listing" border="0" width="14" height="14" /></a> </td> </tr> </table> <?php } ?> <br> <table width="100%" cellpadding="0" cellspacing="0" border="0"> <tr> <td class="td3"> <b>Your Listings</b></td> </tr> </table> <?php $SQL = "SELECT * from $table $qstring"; $result = @mysql_query( $SQL ); while( $row = @mysql_fetch_array( $result ) ) { $check = 1; ?> <table width="100%" cellpadding="0" cellspacing="0" border="0"> <tr> <?PHP $filename = "$svr_rootscript/product_images/thumb/$row[IMAGENAME].jpg"; if (file_exists($filename)) { ?> <td nowrap class="td4" align="center" valign="top"> <a href="?page=add_photos&id=<?php echo $row[ID]; ?>"><img src="../product_images/thumb/<?php echo "$row[IMAGENAME]"; ?>.jpg" border="1" /></a> </td> <?PHP } ?> <td class="td4" colspan="2" width="100%"><b> <?php echo "<a href = \"?page=$setpage&id=$row[ID]&qt=update\">$row[TITLE]</a>"; ?> </b><br> <span class="font3">Category(s): <?PHP $pieces = explode("-", $row[CATEGORY]); foreach ($pieces as $cats => $value) { if ($value != $nil && $value != '-' && $value != '--') { $SQLCat = "SELECT * from CATEGORIES WHERE ID = '$value'"; $resultCat = @mysql_query( $SQLCat ); $rowCat = @mysql_fetch_array( $resultCat ); echo "$rowCat[TITLE], "; } } ?> </span> </td> <td nowrap class="td4"><a href="?page=add_photos&id=<?php echo $row[ID]; ?>"><img src="../images/Iphoto.png" width="22" height="22" border="0"></a></td> <td class="td4"><a href="<?php echo "?page=$setpage&id=$row[ID]&qt=insert&clone=1"; ?>"> <img src="../images/user-group3.gif" border="0" alt="Edit this listing" width="14" height="14" /></a> </td> <td class="td4"><a href="<?php echo "?page=$setpage&id=$row[ID]&qt=update"; ?>"> <img src="../images/edit-comment-orange.gif" border="0" alt="Edit this listing" width="14" height="14" /></a> </td> <td class="td4"><a href="../library/delete.php?id=<?php echo $row[ID]; ?>&return=<?php echo $return; ?>&type=<?php echo "$table"; ?>"> <img src="../images/delete-page-red.gif" alt="Delete this listing" border="0" width="14" height="14" /></a> </td> </tr> </table> <?php } ?> <?PHP if (empty($check)) { ?> <table width="100%" cellpadding="0" cellspacing="0" border="0"> <tr> <td nowrap class="td4">No listings found.</td> </tr> </table> <?PHP } ?> Hey there, I have never really done pagination before and I was wondering if anyone can take a look at my code and help me with coding pagination? This database will be storing a lot of information and I am going to have the need to break it into multiple pages. The following code is the code from my results page, which is where I assume I will need to code the pagination. This is the page that displays all of the results after everything is processed and entered into the database in a different php file. <?php $username="removed_for_this_post"; //Database Username $password="removed_for_this_post"; //Database Password $database="removed_for_this_post"; //Database Name mysql_connect("localhost",$username,$password); //Connection to database @mysql_select_db($database) or die("ALERT! Database not found!"); //Selection of database $query="SELECT * FROM leads ORDER by id DESC"; //Database table to query $result=mysql_query($query); $num=mysql_numrows($result); mysql_close(); ?> <html> <head> <link href="db_style.css" rel="stylesheet" type="text/css" media="screen" /> </head> <body> <div id="wrap"> <div id="header"> <div id="hdr_content"> </div> </div> <br><br><br><br> <center> <table border="1" bordercolor="#000000" cellspacing="2" cellpadding="10"> <tr> <th bgcolor="#01337f"><font face="Arial, Helvetica, sans-serif" size="2">Name</font></th> <th bgcolor="#01337f"><font face="Arial, Helvetica, sans-serif" size="2">E-Mail</font></th> <th bgcolor="#01337f"><font face="Arial, Helvetica, sans-serif" size="2">Age</font></th> <th bgcolor="#01337f"><font face="Arial, Helvetica, sans-serif" size="2">Gender</font></th> <th bgcolor="#01337f"><font face="Arial, Helvetica, sans-serif" size="2">Location</font></th> <th bgcolor="#01337f"><font face="Arial, Helvetica, sans-serif" size="2">Home Phone</font></th> <th bgcolor="#01337f"><font face="Arial, Helvetica, sans-serif" size="2">Other Phone</font></th> <th bgcolor="#01337f"><font face="Arial, Helvetica, sans-serif" size="2">Best Time to Reach</font></th> <th bgcolor="#01337f"><font face="Arial, Helvetica, sans-serif" size="2">Referrer</font></th> <th bgcolor="#01337f"><font face="Arial, Helvetica, sans-serif" size="2" color="#FFFFFF">Options</font></th> </tr> </div> <div id="footer"> <div class="footer_content"> <font class="footer_header"> </div> </div> <?php $i=0; while ($i < $num) { $id=mysql_result($result,$i,"id"); //Unique ID Field $name=mysql_result($result,$i,"name"); //Name $email=mysql_result($result,$i,"email"); //EMail Address $age=mysql_result($result,$i,"age"); //Age $gender=mysql_result($result,$i,"gender"); //Gender $location=mysql_result($result,$i,"location"); //City of Residence $homephone=mysql_result($result,$i,"homephone"); //Home Phone Number $otherphone=mysql_result($result,$i,"otherphone"); //Secondary Phone Number $besttime=mysql_result($result,$i,"besttime"); //Best Time to Reach $referrer=mysql_result($result,$i,"referrer"); //Referrer ?> <tr> <td align="center" bgcolor="#CCCCCC"><font class="lead_txt"><? echo $name; ?></font></td> <td align="center" bgcolor="#CCCCCC"><font face="Arial, Helvetica, sans-serif" size="2" class="lead_txt"><? echo $email; ?></font></td> <td align="center" bgcolor="#CCCCCC"><font face="Arial, Helvetica, sans-serif" size="2" class="lead_txt"><? echo $age; ?></font></td> <td align="center" bgcolor="#CCCCCC"><font face="Arial, Helvetica, sans-serif" size="2" class="lead_txt"><? echo $gender; ?></font></td> <td align="center" bgcolor="#CCCCCC"><font face="Arial, Helvetica, sans-serif" size="2" class="lead_txt"><? echo $location; ?></font></td> <td align="center" bgcolor="#CCCCCC"><font face="Arial, Helvetica, sans-serif" size="2" class="lead_txt"><? echo $homephone; ?></font></td> <td align="center" bgcolor="#CCCCCC"><font face="Arial, Helvetica, sans-serif" size="2" class="lead_txt"><? echo $otherphone; ?></font></td> <td align="center" bgcolor="#CCCCCC"><font face="Arial, Helvetica, sans-serif" size="2" class="lead_txt"><? echo $besttime; ?></font></td> <td align="center" bgcolor="#CCCCCC"><font face="Arial, Helvetica, sans-serif" size="2" class="lead_txt"><? echo $referrer; ?></font></td> <td bgcolor="#01337f""><a href="db_edit.php?id=<?php echo $id; ?>"><img src="edit.png" width="25" height="25" alt="Edit"></a> <a href="db_remove.php?id=<?php echo $id; ?>"><img src="delete.png" width="25" height="25" alt="Delete"></a> <a href="email_lead.php?id=<?php echo $name, $email; ?>"><img src="email.png" width="25" height="25" alt="E-Mail"></a></td> </tr> </center> </font> </body> </html> <?php $i++; } echo "</table>"; ?> 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 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. Alright, I need to find a way to display the top ten duplicated rows for a table in my database. I have tried multiple methods but I have failed. Can you please assist me with this problem. I currently run a query in phpmyadmin to get the result, but i cant figure out how to properly code the php script. Hi, I use this code (found in post http://www.phpfreaks.com/forums/index.php?topic=205633.0) to create a tree structure of categories and subgategories. Code: [Select] $prevcat = ''; $prevsubcat = ''; $sql = "SELECT * FROM $tbl_name ORDER BY categ, subcateg"; $result = mysql_query($sql) or trigger_error(mysql_error(),E_USER_ERROR); while($row = mysql_fetch_assoc($result)) { $cat = $row['categ']; $subcat = $row['subcateg']; $item = $row['itemname']; $description = $row['itemdesc']; if($cat != $prevcat){ echo $cat.'<br />'; echo 'sc '. $subcat.'<br />';//if the category has changed, we also want to show the new subcat }elseif($subcat != $prevsubcat){ echo $subcat.'<br />'; } echo 'it '.$item.'<br />'; echo 'desc '.$description.'<br />'; $prevcat = $cat; $prevsubcat = $subcat; } The above code works fine but I am trying to figure out how to count results of the deepest subcategory, so I will be able to change cell color or have results presented in two colums (left and right). Example: - main category 1 -- subcategory under category 1 ------ result 1 ------ result 2 ------ result 3 - main category 2 -- subcategory under category 2 ------ result 1 ------ result 2 - main category 3 -- subcategory under category 3 ------ result 1 ------ result 2 ------ result 3 ------ result 4 etc I want to count results under each subcategory, so on the above example I should have: 3 results for category 1 2 results for category 2 4 results for category 3 Any suggestions? Thank you. Hi, I have a database with roughly 73k rows (records). I am using a query string such as: SELECT DISTINCT Column FROM databasename I am then using mysql_num_rows($results) to count the number of distinct rows. This method works, but it is slow. Is there a faster way of doing this perhaps internal to the mysql without having to send the entire results to the PHP when I only need a count? I'm creating a login that uses PDO statements, however when I try to count how many rows was selected, it gives me an error mentioning its an unidentified property, I always tried rowCount, but it did not work either... Notice: Undefined property: PDOStatement::$num_rows in /Users/JPFoster/Sites/Jaipai.Blog/engine.php on line 50 Here's my class method: Code: [Select] public function login($user, $pw){ $user = addslashes($this->user); $pw = md5($pw); $query = "SELECT * FROM `users` WHERE `username` = '".$user."' AND `password` = '".$pw."'"; //set up query $results = $this->pdo->query($query); if ($results->num_rows == 1){ $row = $results->fetch_assoc(); echo "Worked!"; //$this->set_session($row['id'], $row['username'], $row['email']); } else { echo "Your Username and Password did not match"; } //if user + passs match { redirect (member page) and set up sessions // Else ... Retry... } Any help with this will be greatly appreciated! 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?? } ?> 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 What would be the correct way to close a mysql query? At current the second query below returns results from the 1st query AND the 2nd query The 3rd query returns results from the 1st, 2nd and 3rd query. etc etc. At the moment I get somthing returned along the lines of... QUERY 1 RESULTS Accommodation 1 Accommodation 2 Accommodation 3 QUERY 2 RESULTS Restaurant 1 Restaurant 2 Restaurant 3 Accommodation 1 Accommodation 2 Accommodation 3 QUERY 3 RESULTS Takeaways 1 Takeaways 2 Takeaways 3 Restaurant 1 Restaurant 2 Restaurant 3 Accommodation 1 Accommodation 2 Accommodation 3 Code: [Select] <!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=utf-8" /> <?php include($_SERVER['DOCUMENT_ROOT'].'/include/db.php'); ?> <title>Untitled Document</title> <style type="text/css"> <!-- --> </style> <link href="a.css" rel="stylesheet" type="text/css" /> </head><body> <div id="listhold"> <!------------------------------------------------------------------------------------------------------------------------------------------------------> <div class="list"><a href="Placestostay.html">Places To Stay</a><br /> <?php $title ="TITLE GOES HERE"; $query = mysql_query("SELECT DISTINCT subtype FROM business WHERE type ='Accommodation' AND confirmed ='Yes' ORDER BY name"); echo mysql_error(); while($ntx=mysql_fetch_row($query)) $nt[] = $ntx[0]; $i = -1; foreach($nt as $value) {$i++; $FileName = str_replace(' ','_',$nt[$i]) . ".php"; $FileUsed = str_replace('_',' ',$nt[$i]); echo "<a href='" . str_replace(' ','_',$nt[$i]) . ".php?title=$title&subtype=$FileUsed'>" . $nt[$i] . "</a>" . "<br/>"; $FileHandle = fopen($FileName, 'w') or die("cant open file"); $pageContents = file_get_contents("header.php"); fwrite($FileHandle,"$pageContents");} fclose($FileHandle); ?> </div> <!------------------------------------------------------------------------------------------------------------------------------------------------------> <div class="list"><a href="Eatingout.html">Eating Out</a><br /> <?php $title ="TITLE GOES HERE"; $query = mysql_query("SELECT DISTINCT subtype FROM business WHERE type ='Restaurant' AND confirmed ='Yes' ORDER BY name"); echo mysql_error(); while($ntx=mysql_fetch_row($query)) $nt[] = $ntx[0]; $i = -1; foreach($nt as $value) {$i++; $FileName = str_replace(' ','_',$nt[$i]) . ".php"; $FileUsed = str_replace('_',' ',$nt[$i]); echo "<a href='" . str_replace(' ','_',$nt[$i]) . ".php?title=$title&subtype=$FileUsed'>" . $nt[$i] . "</a>" . "<br/>"; $FileHandle = fopen($FileName, 'w') or die("cant open file"); $pageContents = file_get_contents("header.php"); fwrite($FileHandle,"$pageContents");} fclose($FileHandle); ?> </div> <!------------------------------------------------------------------------------------------------------------------------------------------------------> <div class="list"><a href="Eatingin.html">Eating In</a><br /> <?php $title ="TITLE GOES HERE"; $query = mysql_query("SELECT DISTINCT subtype FROM business WHERE type ='Takeaways' AND confirmed ='Yes' ORDER BY name"); echo mysql_error(); while($ntx=mysql_fetch_row($query)) $nt[] = $ntx[0]; $i = -1; foreach($nt as $value) {$i++; $FileName = str_replace(' ','_',$nt[$i]) . ".php"; $FileUsed = str_replace('_',' ',$nt[$i]); echo "<a href='" . str_replace(' ','_',$nt[$i]) . ".php?title=$title&subtype=$FileUsed'>" . $nt[$i] . "</a>" . "<br/>"; $FileHandle = fopen($FileName, 'w') or die("cant open file"); $pageContents = file_get_contents("header.php"); fwrite($FileHandle,"$pageContents");} fclose($FileHandle); ?> </div> <!------------------------------------------------------------------------------SKILLED TRADES BELOW---------------------------------------------------> <div class="list"><a href="Skilledtrades.html">Skilled Trades</a><br/> <?php $title ="TITLE GOES HERE"; $query = mysql_query("SELECT DISTINCT subtype FROM business WHERE type ='Skilled Trades' AND confirmed ='Yes' ORDER BY name"); echo mysql_error(); while($ntx=mysql_fetch_row($query)) $nt[] = $ntx[0]; $i = -1; foreach($nt as $value) {$i++; $FileName = str_replace(' ','_',$nt[$i]) . ".php"; $FileUsed = str_replace('_',' ',$nt[$i]); echo "<a href='" . str_replace(' ','_',$nt[$i]) . ".php?title=$title&subtype=$FileUsed'>" . $nt[$i] . "</a>" . "<br/>"; $FileHandle = fopen($FileName, 'w') or die("cant open file"); $pageContents = file_get_contents("header.php"); fwrite($FileHandle,"$pageContents");} fclose($FileHandle); ?> </div> I have 2 queries that I want to join together to make one row
I have a variable called clientid being passed via a url. I place that in a variable called $clientid I have two tables, client and web_info I want to select all from client and web_info for the appropriate clientid. I may not have my tables set up correctly. The primary key on the table client is auto genereated and titled client_id the primary key on web_info is e_mail and I want to pull information from the table web_info where the e_mail columns match. Below is my PHP: $query = "SELECT * FROM client WHERE $clientid = client_id and FROM web_info WHERE client.e_mail = web_info.e_mail"; $result = mysqli_query($connection,$query); $row = mysqli_fetch_array($result); I know I probably screwed all this up but please help I'm trying to do a following feed. This feed includes posts and comments that the user is following.
The first 3 following types are in the content table and the last following type is in the comments table.
This query fetches the first 3 following types successfully.
$construct = $connectdb->prepare("SELECT parent.*
FROM `content` as parent
JOIN followers ftable on (
(ftable.parent_id=parent.sid AND ftable.userposts='1')
OR
(ftable.`parent_id`=parent.pageid AND ftable.topic='1')
OR
(ftable.parent_id=parent.pageid AND ftable.page='1')
) AND ftable.userid=:userid
WHERE parent.deleted='0'
GROUP BY parent.id
ORDER BY parent.posted DESC");
This query fetches the following comments type successfully
$construct = $connectdb->prepare("SELECT parent.*
FROM `comments` as parent
JOIN followers ftable on ftable.parent_id=parent.postid AND ftable.comments='1' AND ftable.userid=:userid
WHERE parent.deleted='0'
ORDER BY parent.posted DESC");
How do i combine the two queries? In this feed i want the results to be ORDER by posted DESC.here is some simple code for getting and displaying fata from a database Code: [Select] $sql="SELECT * FROM messages WHERE m_id = '".$id."'"; $result = mysql_query($sql); <table border='0' cellspacing="4"> while($row = mysql_fetch_array($result)) { echo "<tr>"; echo "<th>Message</th>"; echo "</tr>"; echo "<tr>"; echo "<td>" . $row['message'] . "</td>"; echo "</tr>"; } now ive used that while loop to display those results how can i do that again without having a new SQL statement. i cant do 2 while loops because the first one has already got to the end of the amount of rows basically how can i have another while loop displaying those same results again? I am writing a CRON job that will execute daily. First it will identify from a MySql table the date in a field 'FAPforSale_repost35' If the date is the today date it will then execute commands to delete photo images in a directory, delete the directory, and finally remove the record from the database.
I am on step one which is to build the array of records that match the days date. When I run the code, there are no errors but I am not getting results even though the records in the test table are set for today. Below is the select
<?php
define( "DIR", "../zabp_employee_benefits_processor_filesSm/", true );
require( '../zipconfig.php' );
require( DIR . 'lib/db.class.php' );
require_once( $_SERVER['DOCUMENT_ROOT'] . '/_ZABP_merchants/configRecognition.php' );
require_once( $_SERVER['DOCUMENT_ROOT'] . '/_ZABP_merchants/libRecognition/MailClass.inc' );
$todayRepost35 = date("Y-m-d");
echo $todayRepost35;
function repostEndSelect()
{
global $db;
$this->db = $db;
$data = $this->db->searchQuery( "SELECT `FAPforSale_IDnumber`, `FAPforSale_image1`, `FAPforSale_image2`, `FAPforSale_image3`, `FAPforSale_repost35` FROM `FAP_forSaleTest` Where `FAPforSale_repost35` = '$todayRepost35' ");
$this->FAPforSale_IDnumber = $data[0]['FAPforSale_IDnumber'];
$this->FAPforSale_image1 = $data[0]['FAPforSale_image1'];
$this->FAPforSale_image2 = $data[0]['FAPforSale_image2'];
$this->FAPforSale_image3 = $data[0]['FAPforSale_image3'];
$this->FAPforSale_repost35 = $data[0]['FAPforSale_repost35'];
echo $this->FAPforSale_IDnumber;
echo $this->FAPforSale_image1;
echo $this->FAPforSale_image2;
echo $this->FAPforSale_image3;
echo $this->FAPforSale_repost35;
} // ends function...
echo( ' Finished...' );
?>
Thanks in advance for any suggestions or direction. Chapter two will be when I start testing the commands to delete.
This topic has been moved to MySQL Help. http://www.phpfreaks.com/forums/index.php?topic=354388.0 Can someone please give me some ideas as to what might be wrong with this query...I keep getting no result and am echoing $count for debugging (and usernames and passwords) but get nothing for $count (expecting a 0 or a 1) or $dbusername or $dbpassword $sql="SELECT * FROM `users` WHERE `User name` = '$fusername' and `Password` = '$fpassword'"; $result=mysql_query($sql); $dbusername=mysql_result($result,0,"User name"); $dbpassword=mysql_result($result,0,"Password"); echo $dbusername; echo $dbpassword; // Mysql_num_row is counting table row $count=mysql_num_rows($result); // If result matched $fusername and $fpassword, table row must be 1 row echo $fusername; echo $fpassword; echo $count; if($count==1){ (This is where I keep going to the else) quick question, I have this code that returns over 100 buttons: Code: [Select] <?php // Query member data from the database and ready it for display $sql = mysql_query("SELECT * FROM products"); while($row = mysql_fetch_array($sql)){ $product = $row["product"]; $id =$row["id"]; $price =$row["price"]; ?> <div id="products"> <form action="" method="POST" name="myform<?php echo $id; ?>" class="myform<?php echo $id; ?>"> <input type="hidden" name="hiddenField" class="hiddenField" value="<?php echo $product; ?>" /> <input type="hidden" name="hiddenField2" class="hiddenField2" value="<?php echo $id; ?>" /> <input type="hidden" name="hiddenField1" class="hiddenField1" value="<?php echo $price; ?>" /> <input type="submit" name="submit" class="submit" value="<?php echo $product; ?>" style="background-color:lightgreen; height:50px; width:100px;"> </form> </div> <?php } ?> What I would like is for the buttons to form columns of nine. ie 9 buttons in a column then a new column form.... how do I do this? |
|||||||