Jump to content

HDFilmMaker2112

Members
 View Profile  See their activity

  • Posts

    547

  • Joined

  • Last visited

    Never

 Content Type 

Posts posted by HDFilmMaker2112

    ORDER BY AVG()

    in MySQL Help

    Posted

    I need to alter this query so that it takes the AVERAGE of $sort_by_selected2.

     

    $sort_by_selected2 can have two different values here. Either "ABS(review_product_rating) DESC" or "ABS(review_product_rating) ASC"

     

    $sql30 = "SELECT * FROM $tbl_name JOIN $tbl_name3 USING(product_id) WHERE product_category='$cat' ORDER BY $sort_by_selected2 LIMIT $start, $limit";

    Issue with table JOIN query

    in PHP Coding Help

    Posted

    Well I narrowed down the problem to being that there are no reviews in the reviews table. So I did this:

    if($_GET['sort_by']=="rating_lh" || $_GET['sort_by']=="rating_hl"){
$sql30 = "SELECT * FROM $tbl_name JOIN $tbl_name3 USING(product_id) WHERE product_category='$cat' ORDER BY $sort_by_selected2 LIMIT $start, $limit";
$result30 = mysql_query($sql30) or die("Problem with the query: $sql30<br />" . mysql_error());
if(mysql_num_rows($result30)==0){
$sort_by_selected2="product_id";
$sql30 = "SELECT * FROM $tbl_name WHERE product_category='$cat' ORDER BY $sort_by_selected2 LIMIT $start, $limit";
$result30 = mysql_query($sql30) or die("Problem with the query: $sql30<br />" . mysql_error());
}
}
else{
$sql30 = "SELECT * FROM $tbl_name WHERE product_category='$cat' ORDER BY $sort_by_selected2 LIMIT $start, $limit";
$result30 = mysql_query($sql30) or die("Problem with the query: $sql30<br />" . mysql_error());
}

     

    It's only returning the one product with the review though... I want the products that don't have a review to be listed after the first one. I guess the best work around is to generate a blank review, when ever a new product is entered into the system.

    Issue with table JOIN query

    in PHP Coding Help

    Posted

    The below should be getting information from $tbl_name3 to ORDER the information gotten from $tbl_name.

     

    $tbl_name is the products table. $tbl_name3 is the reviews table. I need to get the review_product_rating from the reviews table and order the products by the review_product_rating. 

    $sql30 = "SELECT * FROM $tbl_name JOIN $tbl_name3 USING(product_id) WHERE product_category='$cat' ORDER BY $sort_by_selected2 LIMIT $start, $limit";
$result30 = mysql_query($sql30) or die("Problem with the query: $sql30<br />" . mysql_error());

     

    That code is returning no results, but the query itself isn't throwing any errors.

     

     

    EDIT: something else I just remembered is, the review_product_rating stored in the DB is for each individual review, so I will need to also AVG the review_product_rating results for each products.

    tenary operators

    in PHP Coding Help

    Posted

    It ends up removing the HTML tag and only prints ./index.php.

     

    $sort_by='<form action="'.isset($_GET['q']) ? './index.php' : './store.php'.'" method="GET">';

     

    results in this:

     

    <div class="number_pages_wrapper">
<div class="number_products">./index.php

<input type="hidden" name="q" value="emf-meter" />
<input type="hidden" name="num_products" value="20" />
<label>Sort By:</label>
  <select name="sort_by" onchange="this.form.submit();">
    <option value="relevance" selected="selected">Relevance</option>
    <option value="product_price_hl" >Price: High to Low</option>
    <option value="product_price_lh" >Price: Low to High</option>
  </select>

<noscript>
<input type="submit" value="Go" />
</noscript>
</form>

    tenary operators

    in PHP Coding Help

    Posted

    I'm looking to clean up my code a little bit. With something like this:

     

    $sort_by='
<form action="';
$sort_by.=isset($_GET['q']) ? './index.php' : './store.php';
$sort_by.='" method="GET">';

     

    Is it some how possible to get it to look like this?:

    $sort_by='<form action="'.isset($_GET['q']) ? './index.php' : './store.php'.'" method="GET">';

    Every time I do the second method, I get an error.

    Commas

    in PHP Coding Help

    Posted

    You should have tested it; it won't work.

     

    Okay, what you suggested works, but can you explain what's going on here? I'm not too familiar with implode and have no idea what the code does. I noticed the array $tmp gets carried outside of the while loop and the while loop constructs the array, but how is the comma getting excluded from the last name and how could i place an "and" in there?

     

    implode(); takes an array, iterates through each array item and applies the separator in between each array item, once it detects the last array item, it doesn't apply another separator because it's the end of the array.

     

    http://php.net/implode

     

    Basically does something like this, but you can call the implode() function instead of needing to do this:

    $a=array("a","b","c","d","e");
for($i=0; $i<=count($a); $i++{
if($i!=$count){
echo $i.",";
}
else{
echo $i;
}
}

     

     

    SELECT DISTINCT COUNT (*)?

    in PHP Coding Help

    Posted 

    $query5 = "SELECT COUNT(DISTINCT product_id) as num FROM $tbl_name JOIN $tbl_name2 USING(product_id) WHERE $likeValues";
$total_pages = mysql_fetch_array(mysql_query($query5));
$total_pages = $total_pages['num'];

     

    Works. Thanks.

     

    EDIT:

    Trimmed it down to this:

     

    $query5 = "SELECT COUNT(DISTINCT product_id) as num FROM $tbl_name2 WHERE $likeValues";

    I'm not actually pulling any product information, just counting how many results are returned based on keywords in the query. So it seems useless to query a second table when nothings pulled from it.

    SELECT DISTINCT COUNT (*)?

    in PHP Coding Help

    Posted

    I'm trying to come up with the MySQL way of doing this:

    $likeValues = "$tbl_name2.keyword LIKE '%" . implode("%' OR $tbl_name2.keyword LIKE '%", $keywords) . "%'";

$query5 = "SELECT DISTINCT product_id FROM $tbl_name2 WHERE $likeValues";
$total_pages = mysql_num_rows(mysql_query($query5));

     

    That returns the results I want, allowing me to generate a "Showing 1 -4 out of 7 results".

     

    If I use this:

    $query5 = "SELECT COUNT(*) as num FROM $tbl_name JOIN $tbl_name2 USING(product_id) WHERE $likeValues";
$total_pages = mysql_fetch_array(mysql_query($query5));
$total_pages = $total_pages['num'];

     

    It returns "Showing 1 -4 out of 14 results" or "Showing 1 -4 out of 24 results". Depending upon what the search query is, there are only 7 products in the database. 

     

    If I add "GROUP BY product_id" it throws an error saying Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource.

     

    I'm looking to see if this can be done in a SQL query, as I know it would return results faster.

    Issue with pagination from search results

    in PHP Coding Help

    Posted

    I'm trying to add pagination to my search script.

     

    When I have this in the URL:

    http://ghosthuntersportal.com/index.php?q=emf-meters&num_products=4

     

    It only returns 4 results. When you put a 6 in the URL it returns 6 products. It should be return 7 over two pages. I'm also using the same script (it's being called via an include), on another portion of the site and it's working perfectly.

     

     

    relative code for search page:

    elseif(isset($_GET['q'])){
$search=$_GET['q'];
$section="- Search - Query: $search";
$crumbs='<a href="./index.php">Home</a> <span class="eleven">&#187;</span> <a href="./index.php?q='.$search.'">Search</a> <span class="eleven">&#187;</span> Query: ';
$crumbs.=implode(" ",explode("-",$search));
$search = stripslashes($search);
$search = mysql_real_escape_string($search);
$keywords=explode("-",$search);

include 'useri.php';
$likeValues = "$tbl_name2.keyword LIKE '%" . implode("%' OR $tbl_name2.keyword LIKE '%", $keywords) . "%'";

$sql10000= "SELECT * FROM $tbl_name JOIN $tbl_name2 USING(product_id) WHERE $likeValues GROUP BY product_id ORDER BY $sort_by_selected2 LIMIT $start, $limit";
$result10000=mysql_query($sql10000);
if(mysql_num_rows($result10000)==0){
$content='<div class="center">Search found no results.</div>';
}
else{
$total_pages = mysql_num_rows($result10000);
while($row10000=mysql_fetch_array($result10000)){
extract($row10000);
$product_name=ucwords($product_name);
$content.='<div class="content_text3">
<div class="product2"><div><a href="./store.php?product='.$product_id.'"><img src="/images/'.$product_image.'" alt="'.$product_name.'" class="product_image" /></a></div>
<div><a href="./store.php?product='.$product_id.'">'.$product_name.'</a></div>
<div>Price: $'.$product_price.'</div>
<div class="add_to_cart">'.$product_link.'</div></div></div>';
}
$records_on_page = mysql_num_rows($result10000);
$start_record = $start + 1;
$end_record = $start_record + $records_on_page -1;
$products_on_page="Showing ".$start_record." - ".$end_record." out of ".$total_pages." product";
if($total_pages > 1){
$products_on_page.="s";
}
}
}

     

     

    Pagination and drop-down menus code.

    <?php

if(isset($_GET['num_products']) && ctype_digit($_GET['num_products'])){
$num_products_per_page=(int)$_GET['num_products'];
$_SESSION[$cat2]['num_products_per_page'] = $num_products_per_page;
$num_products_per_page = stripslashes($num_products_per_page);
$num_products_per_page = mysql_real_escape_string($num_products_per_page);
}
else{
$num_products_per_page="20";
}
$num_products='
<form action="';
$num_products.=isset($_GET['q']) ? './index.php' : './store.php';
$num_products.='" method="GET">';
if(isset($_GET['q'])){
$num_products.='
<input type="hidden" name="q" value="'.$_GET['q'].'" />';
}
if(isset($_GET['cat'])){
$num_products.='<input type="hidden" name="cat" value="'.$cat2.'" />
';
}
if(isset($_GET['sort_by'])){
$num_products.='<input type="hidden" name="sort_by" value="'.$_GET['sort_by'].'" />';
}
$num_products.='
  <label>Display:</label>
  <select name="num_products" onchange="this.form.submit();">
    <option value="8" ';
$num_products.=$_GET['num_products']==8 ? 'selected="selected"' : '';
$num_products.='>8 Items per Page</option>
    <option value="12" ';
$num_products.=$_GET['num_products']==12 ? 'selected="selected"' : '';
$num_products.='>12 Items per Page</option>
    <option value="16" ';
$num_products.=$_GET['num_products']==16 ? 'selected="selected"' : '';
$num_products.='>16 Items per Page</option>
<option value="20" ';
$num_products.=$_GET['num_products']==20 ? 'selected="selected"' : !isset($_GET['num_products']) ? 'selected="selected"' : '';
$num_products.='>20 Items per Page</option>
    <option value="24" ';
$num_products.=$_GET['num_products']==24 ? 'selected="selected"' : '';
$num_products.='>24 Items per Page</option>
<option value="32" ';
$num_products.=$_GET['num_products']==32 ? 'selected="selected"' : '';
$num_products.='>32 Items per Page</option>
    <option value="40" ';
$num_products.=$_GET['num_products']==40 ? 'selected="selected"' : '';
$num_products.='>40 Items per Page</option>
<option value="48" ';
$num_products.=$_GET['num_products']==48 ? 'selected="selected"' : '';
$num_products.='>48 Items per Page</option>
  </select>
<noscript>
<input type="submit" value="Go" />
</noscript>
</form>
';

if(isset($_GET['sort_by'])){
$sort_by_selected=$_GET['sort_by'];
$_SESSION[$cat2]['sort_by_selected'] = $sort_by_selected;
$sort_by_selected = stripslashes($sort_by_selected);
$sort_by_selected = mysql_real_escape_string($sort_by_selected);
if(isset($sort_by_selected)){
$sort_by_selected2=$sort_by_selected;
if($sort_by_selected2=="product_price_hl"){
$sort_by_selected2="ABS(product_price) DESC";
}
elseif($sort_by_selected2=="product_price_lh"){
$sort_by_selected2="ABS(product_price) ASC";
}
elseif($sort_by_selected2=="relevance"){
$sort_by_selected2="product_id";
}
}
}
else{
$sort_by_selected2="product_id";
}
$sort_by='
<form action="';
$sort_by.=isset($_GET['q']) ? './index.php' : './store.php';
$sort_by.='" method="GET">';

if(isset($_GET['q'])){
$sort_by.='
<input type="hidden" name="q" value="'.$_GET['q'].'" />';
}
if(isset($_GET['cat'])){
$sort_by.='
<input type="hidden" name="cat" value="'.$cat2.'" />';
}
if(isset($_GET['num_products']) && ctype_digit($_GET['num_products'])){
$sort_by.='
<input type="hidden" name="num_products" value="'.$_GET['num_products'].'" />';
}
$sort_by.='
<label>Sort By:</label>
  <select name="sort_by" onchange="this.form.submit();">
    <option value="relevance" ';
$sort_by.=$_GET['sort_by']=="relevance" ? 'selected="selected"' : !isset($_GET['sort_by']) ? 'selected="selected"' : '';
$sort_by.='>Relevance</option>
    <option value="product_price_hl" ';
$sort_by.=$_GET['sort_by']=="product_price_hl" ? 'selected="selected"' : '';
$sort_by.='>Price: High to Low</option>
    <option value="product_price_lh" ';
$sort_by.=$_GET['sort_by']=="product_price_lh" ? 'selected="selected"' : '';
$sort_by.='>Price: Low to High</option>
  </select>
<noscript>
<input type="submit" value="Go" />
</noscript>
</form>
';

// How many adjacent pages should be shown on each side?
$adjacents = 3;

/* 
First get total number of rows in data table. 
If you have a WHERE clause in your query, make sure you mirror it here.
*/
if(isset($_GET['q'])){
$query5 = "SELECT COUNT(*) as num FROM $tbl_name WHERE product_category='$cat'";
$likeValues = "$tbl_name2.keyword LIKE '%" . implode("%' OR $tbl_name2.keyword LIKE '%", $keywords) . "%'";

$query5 = "SELECT COUNT(*) as num FROM $tbl_name JOIN $tbl_name2 USING(product_id) WHERE $likeValues";
}
else{
$query5 = "SELECT COUNT(*) as num FROM $tbl_name WHERE product_category='$cat'";
}
$total_pages = mysql_fetch_array(mysql_query($query5));
$total_pages = $total_pages['num'];

/* Setup vars for query. */
$targetpage = isset($_GET['q']) ? 'index.php?q='.$_GET['q'].'' : 'store.php?cat='.$_GET['cat'].''; 	
//your file name  (the name of this file)
$targetpage.= isset($num_products_per_page) ? '&num_products='.$num_products_per_page.'' : '';
$targetpage.= isset($sort_by_selected) ? '&sort_by='.$sort_by_selected.'' : '';
$limit = $num_products_per_page;		//how many items to show per page
$page = $_GET['page'];
if($page){
$start = ($page - 1) * $limit; 			//first item to display on this page
}
else{
$start = 0;								//if no page var is given, set start to 0
}


/* Setup page vars for display. */
if ($page == 0){ $page = 1; }				//if no page var is given, default to 1.
$prev = $page - 1;							//previous page is page - 1
$next = $page + 1;							//next page is page + 1
$lastpage = ceil($total_pages/$limit);		//lastpage is = total pages / items per page, rounded up.
$lpm1 = $lastpage - 1;						//last page minus 1

/* 
Now we apply our rules and draw the pagination object. 
We're actually saving the code to a variable in case we want to draw it more than once.
*/
$pagination = "";
if($lastpage > 1){
$pagination .= "<div class=\"pagination\">";
//previous button
if ($page > 1){
$pagination.= '<a href="'.$targetpage.'&page='.$prev.'">&#171; Previous</a>   ';
}
else{
$pagination.= "";
}
//pages	
if ($lastpage < 7 + ($adjacents * 2))	//not enough pages to bother breaking it up
{
for ($counter = 1; $counter <= $lastpage; $counter++)
{
if ($counter == $page){
$pagination.= '<span class="current">'.$counter.'</span>';
}
else{
$pagination.= '<a href="'.$targetpage.'&page='.$counter.'">'.$counter.'</a>';					
}
if($counter!=$lastpage){
$pagination.='<span class="page_num_divider';
$pagination.= $counter%2 == 0 ? '' : '2';
$pagination.='"> | </span>';
}
}
}
elseif($lastpage > 5 + ($adjacents * 2))	//enough pages to hide some
{
//close to beginning; only hide later pages
if($page < 1 + ($adjacents * 2)){
for ($counter = 1; $counter < 4 + ($adjacents * 2); $counter++){
if ($counter == $page){
$pagination.= '<span class="current">'.$counter.'</span>';
}
else{
$pagination.= '<a href="'.$targetpage.'&page='.$counter.'">'.$counter.'</a>';					
}
$pagination.='<span class="page_num_divider';
$pagination.= $counter%2 == 0 ? '' : '2';
$pagination.='"> | </span>';
}
$pagination.= "...";
$pagination.= '<a href="'.$targetpage.'&page='.$lpm1.'">'.$lpm1.'</a>';
$pagination.='<span class="page_num_divider';
$pagination.= $lpm1%2 == 0 ? '' : '2';
$pagination.='"> | </span>';
$pagination.= '<a href="'.$targetpage.'&page='.$lastpage.'">'.$lastpage.'</a>';		
}
//in middle; hide some front and some back
elseif($lastpage - ($adjacents * 2) > $page && $page > ($adjacents * 2)){
$pagination.= '<a href="'.$targetpage.'&page=1">1</a>';
$pagination.='<span class="page_num_divider2"> | </span>';
$pagination.= '<a href="'.$targetpage.'&page=2">2</a>';
$pagination.= "...";
for ($counter = $page - $adjacents; $counter <= $page + $adjacents; $counter++){
if ($counter == $page){
$pagination.= '<span class="current">'.$counter.'</span>';
}
else{
$pagination.= '<a href="'.$targetpage.'&page='.$counter.'">'.$counter.'</a>';					
}
$pagination.='<span class="page_num_divider';
$pagination.= $counter%2 == 0 ? '' : '2';
$pagination.='"> | </span>';
}
$pagination.= "...";
$pagination.= '<a href="'.$targetpage.'&page='.$lpm1.'">'.$lpm1.'</a>';
$pagination.='<span class="page_num_divider';
$pagination.= $lpm1%2 == 0 ? '' : '2';
$pagination.='"> | </span>';
$pagination.= '<a href="'.$targetpage.'&page='.$lastpage.'">'.$lastpage.'</a>';		
}
//close to end; only hide early pages
else
{
$pagination.= '<a href="'.$targetpage.'&page=1">1</a>';
$pagination.='<span class="page_num_divider2"> | </span>';
$pagination.= '<a href="'.$targetpage.'&page=2">2</a>';
$pagination.= "...";
for ($counter = $lastpage - (2 + ($adjacents * 2)); $counter <= $lastpage; $counter++)
{
if ($counter == $page){
$pagination.= '<span class="current">'.$counter.'</span>';
}
else{
$pagination.= '<a href="'.$targetpage.'&page='.$counter.'">'.$counter.'</a>';					
}
if($counter!=$lastpage){
$pagination.='<span class="page_num_divider';
$pagination.= $counter%2 == 0 ? '' : '2';
$pagination.='"> | </span>';
}
}
}
}

//next button
if ($page < $counter - 1){
$pagination.= '   <a href="'.$targetpage.'&page='.$next.'">Next &#187;</a>';
$pagination.= "</div>\n";
}
else{
$pagination.= "";
$pagination.= "</div>\n";		
}
}

?>

     

    For the most part the above works... it just won't show the proper "Showing _ - _ out of 7 products" and produce the proper pagination in the top and bottom right corners. Hoping I don't have to screw with the Pagination code, because it's working perfectly for the other page.

    form submit redirecting unexpectedly

    in PHP Coding Help

    Posted

    No it isn't. Just look at the URL in the address bar after changing the sort option, and you can see that it is no longer there. Why do you have two separate sets of <form></form> tags in that script?

     

    I can't see the URL in the address bar, because it takes off to index.php.

     

    And what do you mean two sets? One's for the Sort By form and the other for Display form.

    How to save somebody's IP in text file

    in PHP Coding Help

    Posted

    Get their IP address, put it into an array, count the array, if less than one, write IP.

     

     

    $a=array(IP DETAILS)
if(count($a) < 1){
Do write IP.
}
else{
//Leave Blank.
}

     

    Use fwrite to write the IP address. Don't know about putting into the same file. I don't know exactly how to process the IP stuff, and how store it in the array. But might get you started.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.