|
PHP - Excluding Records In A Query
When I run this query to exclude records where id = $userarray['id'],
$q = "SELECT * FROM users WHERE inst_id = '". $userarray['inst_id'] ."' AND id != '". $userarray['id'] ."' "; it actually ONLY returns records with $userarray['id'] as opposed to excluding them. Isn't the way to exclude them to use != ? Similar TutorialsI was just wondering if this is a way that you can exclude certain rows from a MySQL query. For example if I have 10 records each with a unique id, is there anyway I can get all of the records except record 5? Thanks for any help. I've got query. Hello just wondering if someone can help me... I have a select multiple form that sends the id numbers of records I want to get... how do I construct the query to the mysql db to do this? $array-$_POST['form-array'] so something like $r = ("SELECT column from 'Table' WHERE ID = (array, values, here)") Thanks for any help. Hi all, I'm trying to make a query that allows the user to edit the name of a entry in SQL. I believe I have the corect Syntax but I'm not sure if I have the code set up correctly. I am using 2 queries when I think it is possible with one. The first query is used to Pint out results for a certain year and the second to update the names. Code: [Select] $desiredYear = $_POST['year']; $nameEdit = $_POST['edit']; $origName = $_POST['orig']; echo ' The year you have chosen is '.$desiredYear; $describeQuery = "SELECT ID, Name, (SELECT SUM(SalesVolume) as SalesVolume FROM MonthlySales WHERE ProductCode=Products.ID AND Year = '$desiredYear') AS num_sales FROM Products"; $editQuery = "UPDATE Products SET Name = '$nameEdit' WHERE Name ='$origName'"; $results = sqlsrv_query($conn, $describeQuery); echo '<table border="1" BORDERCOLOR=Black>'; echo '<tr><th bgcolor = "LightBlue">Name</th><th bgcolor = "LightBlue" >ID</th> <th bgcolor = "LightBlue" >Sales</th></tr>'; while($row = sqlsrv_fetch_array($results, SQLSRV_FETCH_ASSOC)) { echo '<tr>'; echo '<td >' .$row['Name'].'</td>'; echo '<td>' .$row['ID'].'</td>'; echo '<td>' .$row['num_sales'].'</td>'; echo '</tr>'; } echo '</table>'; sqlsrv_close($conn); Can somebody help me with this? Thank you Have a web page that displays a number of update forms depending on the amout of records that get returned from a mysql query, if query returns 4 records then the page will display 4 identical forms, the trouble i'm having is getting the each of the 4 forms to display a different record as at the moment they all show just first record of query. I have played around with loops but not getting anywhere which may be due to my limited knowledge code for page is below Code: [Select] <?php $numberofrow =mysql_num_rows($Recordset1); for($counter = 1;$counter<=$numberofrow;$counter++){ ?> <label for="hometeam2"></label> <form id="form1" name="form1" method="POST" action="<?php echo $editFormAction; ?>"> <label for="hometeam"></label> <input name="fixid" type="text" id="fixid" value="<?php echo $row_Recordset1['FixtureID']; ?>" /> <input name="hometeam" type="text" id="hometeam2" value="<?php echo $row_Recordset1['hometeamname']; ?>" /> <label for="homescore"></label> <input name="homescore" type="text" id="homescore" value="<?php echo $row_Recordset1['homescore']; ?>" /> <label for="awayscore"></label> <label for="fixid"></label> <input name="awayscore" type="text" id="awayscore" value="<?php echo $row_Recordset1['awayscore']; ?>" /> <label for="awayteam"></label> <input name="awayteam" type="text" id="awayteam" value="<?php echo $row_Recordset1['awayteamname']; ?>" /> <input type="submit" name="update" id="update" value="Submit" /> <input type="hidden" name="MM_update" value="form1" /> Hello guys, I have an website where I have 5 category blocks in my index page to show last 3 posts in each category. But I having problems showing the content in my "last added" block. Sometimes works, and sometimes not. Dont know what is happening... Look: http://screensnapr.com/e/Zm4ssA.jpg Only "Filmes" is showing, but not "Series"... the movies block sometimes have this issue too... the code is almost the same as above, maybe is the same problem... For each category that I want to show their posts, I have a file called newhome_x.php In my templates/dirvideo/main.tpl file, I have this code: Code: [Select] <div id="page_content" class="clearfix"> {info} {newhome_final} {content} </div> In my templates/dirvideo/newhome.tpl file, I have this code: Code: [Select] <li style="margin-right: 45px;" onclick="window.location='{link}'"> <a title='{home_title}' href="{link}"><img src="[xfvalue_poster]" alt='{home_title}' /></a> <h3 style="overflow:hidden"><a title='{home_title}' href="{link}">{home_title}</a></h3> </li> in my index.php I have this code: require_once ENGINE_DIR . '/modules/newhome.php'; require_once ENGINE_DIR . '/modules/newhome_b.php'; require_once ENGINE_DIR . '/modules/newhome_c.php'; require_once ENGINE_DIR . '/modules/newhome_d.php'; require_once ENGINE_DIR . '/modules/newhome_e.php'; if (clean_url ( $_SERVER['HTTP_HOST'] ) != clean_url ( $config['http_home_url'] )) { $replace_url = array (); $replace_url[0] = clean_url ( $config['http_home_url'] ); $replace_url[1] = clean_url ( $_SERVER['HTTP_HOST'] ); } else $replace_url = false; if (!$is_logged && !in_array($_GET['do'], array('register', 'lostpassword'))) { include 'index.html'; die; } $tpl->load_template ( 'main.tpl' ); $tpl->set ( '{lastusers}', $lastusers); $tpl->set('{forumStyle}', require_once ROOT_DIR . '/engine/bullet_energy/modules/load_style.php'); require_once ENGINE_DIR . '/modules/3news.php';$source = '<div class="index_top1" style="width: 100%; padding-top: 10px; padding-bottom: 10px; " > <span style="font-size: 24px; margin-left: 10px;"><img border="0" src="templates/dirvideo/images/movies.gif"> </span> <ul class="clearfix"> {newhome} </ul> </div> <div class="index_top2" style="width: 100%; padding-top: 10px; padding-bottom: 10px; " > <span style="font-size: 24px; margin-left: 10px;"><img border="0" src="templates/dirvideo/images/serie.gif"> </span> <ul class="clearfix"> {newhome_b} </ul> </div> <div class="index_top3" style="width: 100%; padding-top: 10px; padding-bottom: 10px; " > <span style="font-size: 24px; margin-left: 10px;"><img border="0" src="templates/dirvideo/images/music.gif"> </span> <ul class="clearfix"> {newhome_c} </ul> </div> <div class="index_top6" style="width: 100%; padding-top: 10px; padding-bottom: 10px; " > <span style="font-size: 24px; margin-left: 10px;"><img border="0" src="templates/dirvideo/images/jogo.gif"> </span> <ul class="clearfix"> {newhome_e} </ul> </div> <div class="index_top4" style="width: 100%; padding-top: 10px; padding-bottom: 10px; " > <span style="font-size: 24px; margin-left: 10px;"><img border="0" src="templates/dirvideo/images/programas.gif"> </span> <ul class="clearfix"> {newhome_d} </ul> </div>'; if (!isset($_REQUEST["catalog"]) && !isset($_REQUEST["newsid"]) && !isset($_REQUEST["do"]) && !isset($_REQUEST["page"]) && !isset($_REQUEST["category"]) && !isset($_REQUEST["cat"]) && !isset($_REQUEST["action"]) && !isset($_REQUEST["id"]) && !isset($_REQUEST["name"]) && !isset($_REQUEST["news_name"]) && !isset($_REQUEST["seourl"]) && !isset($_REQUEST["findpost"]) && !isset($_REQUEST["subaction"])) { $tpl->set ( '{newhome_final}', $source);} else { $tpl->set ( '{newhome_final}', "");} $tpl->set ( '{referer}', $referer_block ); $tpl->set ( '{calendar}', $tpl->result['calendar'] ); $tpl->set ( '{top_news}', $top_news ); $tpl->set ( '{last_news}', $last_news ); $tpl->set ( '{rand_news}', $rand_news ); $tpl->set ( '{archives}', $tpl->result['archive'] ); $tpl->set ( '{tags}', $tpl->result['tags_cloud'] ); $tpl->set ( '{vote}', $tpl->result['vote'] ); $tpl->set ( '{topnews}', $topnews ); $tpl->set ( '{login}', $tpl->result['login_panel'] ); $tpl->set ( '{info}', $tpl->result['info'] ); $tpl->set ( '{speedbar}', $tpl->result['speedbar'] ); $tpl->set ( '{catlist}', $catlist ); $tpl->set ( '{moviepopular}', $tpl->result['moviepopular'] );$tpl->set ( '{newhome}', $tpl->result['newhome'] );$tpl->set ( '{newhome_b}', $tpl->result['newhome_b'] );$tpl->set ( '{newhome_c}', $tpl->result['newhome_c'] );$tpl->set ( '{newhome_d}', $tpl->result['newhome_d'] );$tpl->set ( '{newhome_e}', $tpl->result['newhome_e'] ); In my engine/modules folder I have the files: newhome.php newhome_b.php newhome_c.php newhome_d.php newhome_e.php This is the code of engine/modules/newhome_c.php file (Series block): <?php if (!isset($_REQUEST["catalog"]) && !isset($_REQUEST["newsid"]) && !isset($_REQUEST["do"]) && !isset($_REQUEST["page"]) && !isset($_REQUEST["category"]) && !isset($_REQUEST["cat"]) && !isset($_REQUEST["action"]) && !isset($_REQUEST["id"]) && !isset($_REQUEST["name"]) && !isset($_REQUEST["news_name"]) && !isset($_REQUEST["seourl"]) && !isset($_REQUEST["findpost"]) && !isset($_REQUEST["subaction"])) { if( ! defined( 'DATALIFEENGINE' ) ) { die( "Hacking Attempt!" ); } $is_change = false; if ($config['allow_cache'] != "yes") { $config['allow_cache'] = "yes"; $is_change = true;} $tpl->result['newhome_c'] = dle_cache("newhome_c", $config['skin']); $tpl->load_template( 'newhome.tpl' ); if( strpos( $tpl->copy_template, "[xfvalue_" ) !== false ) { $xfound = true; $xfields = xfieldsload();} else $xfound = false; if ($tpl->result['newhome_c'] === false) { $sql = $db->query( "SELECT id, title, date, alt_name, category,xfields,autor FROM " . PREFIX . "_post WHERE category like '%7%' ORDER BY date DESC LIMIT 3" ); while ($row = $db->get_row($sql)) { $catchecks = explode(",",$row["category"]); $catpass = false; foreach ($catchecks as $catcheck) { if ($catcheck == "7") $catpass = true; } if ($catpass) { $sql2 = $db->query( "SELECT alt_name FROM " . PREFIX . "_category WHERE id='$row[category]'" ); $row2 = $db->get_row($sql2); if( $xfound ) { $xfieldsdata = xfieldsdataload( $row['xfields'] ); foreach ( $xfields as $value ) { $preg_safe_name = preg_quote( $value[0], "'" ); if( empty( $xfieldsdata[$value[0]] ) ) { $tpl->copy_template = preg_replace( "'\\[xfgiven_{$preg_safe_name}\\](.*?)\\[/xfgiven_{$preg_safe_name}\\]'is", "", $tpl->copy_template ); } else { $tpl->copy_template = preg_replace( "'\\[xfgiven_{$preg_safe_name}\\](.*?)\\[/xfgiven_{$preg_safe_name}\\]'is", "\\1", $tpl->copy_template ); } $tpl->copy_template = str_replace( "[xfvalue_{$preg_safe_name}]", stripslashes( $xfieldsdata[$value[0]] ), $tpl->copy_template ); } } if($config['seo_type']=='1') { $link= "$row[id]".'-'."$row[alt_name]".'.html'; } else { $link="$row2[alt_name]".'/'."$row[id]".'-'."$row[alt_name]".'.html'; } $tpl->set( '{home_title}',stripslashes($row['title'])); $tpl->set( '{home_author}', stripslashes($row['autor'])); $tpl->set( '{home_date}', $row['date']); $tpl->set( '{link}',$link); $tpl->compile('newhome_c'); } } create_cache ("newhome_c", $tpl->result['newhome_c'], $config['skin']); $tpl->clear(); $db->free(); } if ($is_change) $config['allow_cache'] = false; } ?> I think that is everything here :-) Can please someone help me? Hi ive been stuck for a while now and i just cant get my head around it so if you could help i'd be well chuffed. I'm trying to update multple rows in a table depending upon the results of another. I have a form that enables me to update a table called fleet. Another table called ships is realted to the fleet table as each ship is in a fleet: fleet ----- Fleetname* Location Detected ships ------ ShipID* Fleetname * Shipname The particular fleet i am editing is parsed as a url parameter. I made a query to search for all the ships that are in the fleet i am editing: Code: [Select] $colname_ShipsInfleet = "-1"; if (isset($_GET['recordID'])) { $colname_ShipsInfleet = (get_magic_quotes_gpc()) ? $_GET['recordID'] : addslashes($_GET['recordID']); } mysql_select_db($database_swb, $swb); $query_ShipsInfleet = sprintf("SELECT * FROM ships WHERE FleetName = %s", GetSQLValueString($colname_ShipsInfleet, "text")); $ShipsInfleet = mysql_query($query_ShipsInfleet, $swb) or die(mysql_error()); $row_ShipsInfleet = mysql_fetch_assoc($ShipsInfleet); $totalRows_ShipsInfleet = mysql_num_rows($ShipsInfleet); It searched the ships WHERE the FleetName (which is the primary key of the fleet table) is parsed as a url parameter. The appropriate ship records that are generated i want to use so a field can be updated. For example im updating a fleet record and and setting the location (Planet Name) to a different value. I want the Ship records that are of that particular fleet to update a field (PlanetName) in ship table when the fleet table is updated. Code: [Select] if ((isset($_POST["MM_update"])) && ($_POST["MM_update"] == "form1")) { $updateSQL = sprintf("UPDATE ships WHERE ShipID = $row_ShipsInfleet['ShipID'] SET PlanetName=%s", GetSQLValueString($_POST['select'], "text")); mysql_select_db($database_swb, $swb); $Result1 = mysql_query($updateSQL, $swb) or die(mysql_error()); $updateSQL = sprintf("UPDATE fleet SET PlanetName=%s, Detected=%s, Faction=%s WHERE FleetName=%s", GetSQLValueString($_POST['select'], "text"), GetSQLValueString(isset($_POST['checkbox']) ? "true" : "", "defined","1","0"), GetSQLValueString($_POST['hiddenField2'], "int"), GetSQLValueString($_POST['hiddenField'], "text")); mysql_select_db($database_swb, $swb); $Result1 = mysql_query($updateSQL, $swb) or die(mysql_error()); I'm really not sure how to proceed here so please if you can help me out that would be ace. Thank You This topic has been moved to MySQL Help. http://www.phpfreaks.com/forums/index.php?topic=325939.0 I am having trouble showing reports for a given date range. Currently if I specify something like 11/03/2010 to 11/05/2010 I get results for all years within that month and day such as I may get results for 11/03/2008 11/03/2009 11/03/2010 11/04/2008 11/04/2009 11/04/2010 11/05/2008 11/05/2009 11/05/2010. I am using the following code $result = mysql_query("SELECT * FROM report WHERE date>='$date_begin' and date<='$date_end' ORDER BY 'date'",$db); I use the following format in my date feild mm/dd/yyyy My script is finally working as intended, but I want to add some additional data results. I am trying to resolve how I can display a count of ONLY the records updated by my query: // START :: Query to replace matches mysql_query("UPDATE orig_codes_1a AS a JOIN old_and_new_codes_1a AS b ON concat(a.orig_code_1, a.orig_code_2) = concat(b.old_code_1, b.old_code_2) SET a.orig_code_1 = b.new_code_1, a.orig_code_2 = b.new_code_2") or die(mysql_error()); // END :: Query to replace matches In this query I count ALL records selection: // START :: Create query to be displayed as final results of original codes table. $result = mysql_query("SELECT * FROM orig_codes_1a") or die(mysql_error()); I want to display a count on this part of the query: ....concat(a.orig_code_1, a.orig_code_2) = concat(b.old_code_1, b.old_code_2).... My application displays posts based on currentness and popularity, in two distinct lists. Users can also vote on posts, after which they are hidden. The post IDs that users have voted on are stored in a cookie. I need to construct a concise query that prevents "popular" posts and posts that have been voted on from appearing within the "new" list. In my current system, the used values are explicitly excluded from appearing in the query with some very inelegant code. $num_posts = 5; $sql = "SELECT * FROM posts ORDER BY popularity LIMIT ".$num_posts; $query = mysql_query($sql); $excluded_ids = array() while($row = mysql_fetch_assoc($query)) { // ...do some stuff... $excluded_ids[] = $row['post_id']; } if(!empty($_COOKIE['voted_on'])) { // example value "12|19|23|26" foreach(explode("|", $_COOKIE['voted_on']) as $val)) { $excluded_ids[] = $val; } } $sql = SELECT * FROM posts WHERE post_id <>"; $num_excluded_ids = count($excluded_ids); $i = 0; foreach($excluded_ids as $val) { $i++; $sql .= $val; if($i <= $num_excluded_ids) { $sql .= " AND post_id <> "; } } $sql .= " ORDER BY date_posted LIMIT ".$num_posts; $query = mysql_query($sql) // ... This method is ugly and probably very slow. What would be a better approach? Thanks in advance for anything you can suggest. Hi, I'm sorry to bother you with this request for help. I bought a XML driven Flash website template with an integrated contact form. Even though the contact.php seems pretty secure I receive emails telling me that someone tried to send emails but they contained no recipient addresses, and therefore no delivery could be attempted. So far, it seems as if the validation process in the php and preg_match command seem to work. Never the less those messages keep getting on my nerve. The Failed delivery emails I get look like this: ---------- Date: Mon, 20 Dec 2010 08:12:09 +0100 Message-Id: <E1PUZuj-0005zp-8c@mydomain.com> To: Subject: X-PHP-Script: www.mydomain.com/contact.php MIME-Version: 1.0 Content-type: text/html; charset=windows-1251 From: ---------- As you can see spam protection by filtering the email characters with preg_match has its limits too. The interesting thing is that the sender (E1PUZuj-0005zp-8c) is (of course) changing but only in the last digits. I received about 200 emails with exact the same beginning. So the bot seems to follow an ongoing pattern. I wonder now, if there is a way to set a command to exclude (or to preg_match the exact) character combinations like "E1PUZ" which lead to not sending the email? I know ... it is only a matter of time till the combinations are different but altering the php code once a month is not as much work as deleting all those failed delivery messages per day. Lets say I have an array of different values... for example, $alreadyViewed = array("hi.jpg","cow.jpg","dog.png"); Lets say my database contains: Code: [Select] animals.jpg cat.jpg cow.jpg hi.jpg dog.png How do you I tell MySQL to only fetch the remaining entries? And ignore everything in the $alreadyViewed array? Hi Guys, I've been using PHP for a few years now but for some reason I can never seem to get my head around arrays, basically I have a blog site that I want to show all posts that have been posted in public categories but ignore those in the private categories, I had the idea to put all the private category ID's into an array and use that to check against the posts, however my array isn't working out the way I'd hoped. This is what I have: $isPrivate = mysql_query("SELECT categoryID FROM mod_cat WHERE isPrivate =1") or die (mysql_error()); while ($isPrivateList = mysql_fetch_array($isPrivate, MYSQL_NUM)) { $ignoreList = $isPrivateList; } However this isn't working at all. I know its wrong I just can't work out how to make it right. This is the mySQL I want to use it with: mysql_query("SELECT COUNT(*) AS total_entries FROM mod_posts WHERE articlePosted =1 AND articleCat != $ignorelist") Could anyone please help me? Thanks in advance. So I have this code: Code: [Select] $skill_query = mysql_query("SELECT * FROM character_stats ORDER BY character_stats.". $fields[$skill] ." DESC LIMIT 0, $end", $c2) or print(mysql_error()); $user_query = mysql_query("SELECT user.username, user.userid, character_stats.uid FROM user, character_stats WHERE character_stats.uid = user.userid ORDER BY character_stats.". $fields[$skill] ." DESC LIMIT 0, $end") or print(mysql_error()); //WHERE user.usergroupid != 6 AND characters.banned = 0 $rank = 1; while($player = mysql_fetch_array($skill_query)){ if($user['userid'] != 1){ continue; } $user = mysql_fetch_array($user_query); if($rank >= $start) output($rank, $user['username'], $user['userid'], $player['combat'], $level, number_format($exp)); $rank++; } Yet for some reason it doesn't work. I know that the Code: [Select] if($user['userid'] != 1){ continue; }Is the reason, but how do I fix it? Basically, my goal is to display these highscores, but omit UserID 1. Thanks. Hi Everyone, I have a rather stupid question, but I haven't been able to find the correct method / syntax to do this. I want to allow users to upload a maximum of 5 photos - less then 5 is OK too. I've been testing the following script and haven't gotten it to ignore error # 4 (no file uploaded): Code: [Select] if ($_FILES['userfile']['error'] == 1 || 2 || 3 || 6 || 7) { echo 'Problem: '; switch ($_FILES['userfile']['error']) { case 1: echo 'File exceeded upload_max_filesize'; break; case 2: echo 'File exceeded max_file_size'; break; case 3: echo 'File only partially uploaded'; break; case 6: echo 'Cannot upload file: No temp directory specified.'; break; case 7: echo 'Upload failed: Cannot write to disk.'; break; } exit; } I will be using a while loop and incrementing through the script (this is simpler version). Does anyone know the proper syntax for the IF statement ' if ($_FILES['userfile']['error'] == 1 || 2 || 3 || 6 || 7)" ? Thanks much, Rick Hello, I need to hide products with same title from WooCommerce shop page and i'm trying to achieve that with a custom loop. For example i have 5 products called "Plex" and the only diferrence between them is the SKU field and the color. I don't care which of the same product will be displayed in the shop page, i just want to display only one of all these products. I created a loop which is working and does hide products with same name but i have a problem. If i set posts_per_page=8 it shows less because it counts the hidden products also.
$args = array(
'post_type' => 'product',
'posts_per_page' => 8
);
$query = new WP_Query($args);
$list = array();
while ($query->have_posts()) : $query->the_post();
if(in_array(get_the_title(), $list)){ continue; }
$list[] = get_the_title();
?>
<li><?php wc_get_template_part( 'content', 'product' ); ?></li>
<?php endwhile;
wp_reset_postdata();
P.S. I don't want to remove these products, i just want to hide them from shop loop! Any ideas what is the problem with the loop? Is there a better way to achieve that? I must be brain dead...I've searched everywhere and can't seem to find a snippet for this. I'm just looking for some code to show a directory listing, but only certain files (by extension) and I don't want to show any sub-directories. I thought I might have been able to do this in the .htaccess file, but I can't find anything that will get rid of the sub-directories. So, I figure there must be a way to get a file listing, check the extension, add it to the page, or skip it if it's a sub-directory or doesn't match the extension. Or, I could just be totally, way off...anyway, any help would be greatly appreciated...I'm fairly new to php. I am have a query with a limit of 3 i want it to echo the first two in one li class and the 3 in another li class. This is what i have, but i know the logic is not right, but i cant figure it out Code: [Select] </div> <?php echo "<ul class='services-list'>"; //echo "<li class='bubble'>"; $i = 1; foreach ($rows_class as $record_class ){ ?> <li class="bubble"><a href="viewclass.php?class_id=<?php echo base64_encode($record_class[class_id]) ?>" /><h6 class="title"><img src="./images/services/picture-services.png" class="picture" alt="logo" /><? echo $record_class[class_title]; ?></h6></a><p><?php $string = $record_class['description']; echo substr(strip_tags($string), 0, 200); ?></p> <?php if( $i % 2 === 0 && $record_class !== end($rows_class) ) { echo "</li>\n<li class='bubble last'>\n";?> <?php } elseif ( $record_class === end($rows_class) ) { echo "</li>\n "; } $i++; } ?> </ul> |
|||||||