|
PHP - Optimize Query In Result - Category - Items
Hi
sorry for my bad English I have this queries but when I using num_queries show over 40 for this code!! how can I optimize it for best performance category table is : ".$prefix."_modules_categories item table is : ".$prefix."_modules $display = $openclose; if ($display == 0) { $show = "none"; $catimg = "<img src=\"$closedimg\" align=\"absmiddle\" border=\"0\">"; } else { $show = "show"; $catimg = "<img src=\"$openimg\" align=\"absmiddle\" border=\"0\">"; } //Categories $result = $db->sql_query("select mcid, mcname FROM ".$prefix."_modules_categories WHERE visible='1' ORDER BY mcposition"); //$coin=0; global $multilingual, $currentlang; while ($row = $db->sql_fetchrow($result)) { $mcid = $row['mcid']; $mcname = $row['mcname']; //$coin++; $mcname1 = $mcname; if(strlen($mcname1) == 0){ if ($multilingual == 1) { include("modules/languages/lang-".$currentlang.".php"); $mcname = "$mcnamed[$mcid]"; } else { include("modules/languages/lang-english.php"); $mcname = "$mcnamed[$mcid]"; } }else{ $mcname = "$mcname1"; } $content .= "</div><table border=0 cellpadding='1' cellspacing=1><tr><td width='16'><a id=\"x".$mcname."\" href=\"javascript:Toggle('".$mcname."','$align');\">$catimg</a></td><td><b>".$mcname."</b></table>\n"; $content .= "<div id=\"".$mcname."\" style=\"display: $show; margin-left: 1em;\">\n"; $result2 = $db->sql_query("SELECT title, custom_title, view, url FROM " . $prefix . "_modules WHERE active='1' AND inmenu='1' AND title!='$def_module' AND mcid='$mcid' ORDER BY custom_title ASC"); while ($row = $db->sql_fetchrow($result2)) { $title = $row['title']; $custom_title = $row['custom_title']; $view = $row['view']; $url = $row['url']; $arraymodules[] = "$title:$custom_title:$view:$url"; $m_title = $title; $m_title2 = ereg_replace("_", " ", $m_title); include("modules/".$title."/language/lang-".$currentlang.".php"); if ($custom_title != ""){$m_title2 = $custom_title;}else{ if(strlen($mudulename) != 0){ $custom_title = "$mudulename"; } $m_title2 = $custom_title; } if ($m_title != $main_module) { $showit = 0; switch($view){ case 0: // All users case $showit = 10; break; case 1: // Registered user access if (is_user($user) || is_admin($admin)) $showit = 1; break; case 2: // Admin only access if (is_admin($admin)) $showit = 2; break; default: // Subscriber only access if (is_user($user) || paid()) $showit = 3; break; } global $db , $prefix; $rowz = $db->sql_fetchrow($db->sql_query("SELECT gtset FROM ".$prefix."_config")); $gtset = intval($rowz['gtset']); if ($showit != 0) { $adds = $_SERVER['SERVER_NAME']; if ($url != "" AND $gtset != 0) { $content .= "<table border=0 cellpadding='1' cellspacing=1><tr><td width='16'><img src=\"$arrowimg\" align=\"absmiddle\" border=\"0\"></td><td><a href=\"$url\" title=\"$m_title2\" onMouseOver=\"self.status=' $m_title2 ';return true\" onmouseout=\"self.status='';return true\">$m_title2</a></td></tr></table>\n"; } else { $content .= "<table border=0 cellpadding='1' cellspacing=1><tr><td width='16'><img src=\"$arrowimg\" align=\"absmiddle\" border=\"0\"></td><td><a href=\"modules.php?name=$m_title\" title=\"$m_title2\" onMouseOver=\"self.status=' $m_title2 ';return true\" onmouseout=\"self.status='';return true\">$m_title2</a></td></tr></table>\n"; } } } $mudulename = ""; } } $content .= "</div>"; $db->sql_freeresult($result); $db->sql_freeresult($result1); Similar TutorialsQuote from: Hollows on August 20, 2010, 11:29:25 AM I have an array that is pulling 10 results from a db like this - <?php $result = mysql_query("SELECT * FROM inbox ORDER BY id DESC LIMIT 0,10"); while ($row = mysql_fetch_array($result)) { <div class="<?php echo $row['class']; ?>"> <?php echo $row['name']; echo $row['message']; ?> </div> basically 'class' is defined in an earlyier part of the script and is used to tell the css what class to display - as I'm sure you can see. What I'd like todo then be able to update the class of a message once I have read it, so update the class table with something like 'read' so the css will then be based on .read not what ever was set by the form, either using a link or form button inside each of the 10 results from the array. I've managed to update one by using - Code: [Select] mysql_query("UPDATE shoutbox SET class = 'read' WHERE id = ''XX"); // XX should be row id But I had to set that myself and point the id to the row id I wanted to change - How can I have that already populated with the row for the id that triggered it? To be clear so it looks like - Name Message read button _____________ Name Message read button _____________ Name Message read button _____________ and so on... Would I be better going by check boxes and one submit button? If so how could I implement that? I can provide the full code I've written if it helps Thank you in advance Hello, I have been searching and trying all sorts of ways to get my sub categories drop down to show the correct info after selecting a category from the main drop down but im getting stuck. It's pulling the categories and sub categories from the mysql database fine but no matter which main category i pick it keeps showing the same result in the sub category. My brother said i need to use some sort of onchange do this so the sub category list refreshs but everything i have tried doesn't work.. PLEASE could some one help. MANY MANY THANKS This is the category form Code: [Select] <select name="job_cats" style="width:165px"> <?PHP $query1 = "SELECT * FROM job_cats"; $result1 = mysql_query($query1) or die ("query 1 failed"); $count1 = mysql_num_rows($result1); for ($i = 0; $i < $count1; $i++) { $row1 = mysql_fetch_array($result1); $job_cats_title1 = $row1['title']; $job_cats_id1 = $row1['id']; echo '<option value="'.$job_cats_id1.'">'.$job_cats_title1.'</option>'; echo $job_cats_id1; } ?> </select> this would echo this in the main category list: cat1 cat2 cat3 cat4 this is the sub category form Code: [Select] <select name="job_sub_cats" style="width:165px"> <?PHP $query1 = "SELECT * FROM job_sub_cats WHERE job_cats_id = $job_cats_id1"; $result1 = mysql_query($query1) or die ("query 1 failed"); $count1 = mysql_num_rows($result1); for ($i = 0; $i < $count1; $i++) { $row1 = mysql_fetch_array($result1); $sub_cats_id1 = $row1['id']; $sub_cats_title1 = $row1['subTitle']; $cats_id1 = $row1['job_cats_id']; echo '<option value="'.$sub_cats_id1.'">'.$cats_id1.'</option>'; } ?> </select> this should echo this: 1 2 3 4 but it keeps echoing just the number 4. THANKS PLEASE HELP ricky Hello all. I am using a premade login script which has some very powerful function built in, the one I am looking at using is as follows:
/**
* query - Performs the given query on the database and
* returns the result, which may be false, true or a
* resource identifier.
*/
function query($query){
return mysqli_query( $this->connection, $query);
}
The issue is, displaying the results. I call is via the following code:
$database->query('SELECT LastName, Firstname FROM Patrons WHERE LastName = "Delude"');
But I have no idea how to display it. I have attempted to echo it out, but I get the following error in the logs:
PHP Catchable fatal error: Object of class mysqli_result could not be converted to string
Hi there
I am new to this forum and not much of a php expert. I hope you can assist me.
My problem is as follows:
I have a column consisting of several values (in this case: sceience, art, humanistics and music)
I would like to use the following mysqli query in order to count the appearances of each value in this column:
$query = "SELECT SUM (IF(department = 'science', 1, 0)) AS science, SUM(IF(department = 'art', 1, 0))-> AS art FROM new2"
I have tried lots of ways to loop the results but couldn't get the it working.
Can anyone suggest any idea?
Thanks
Hanan
Hi everyone. I have this code that suppose to divide the query result 3 columns in a row. Like this : Shoes Hats Jeans Shorts Shirts Belts But for some reason this one doesn't work. It gave me everything in a single row. Like this: Shoes Hats jeans Shorts Shirts Belts. I noticed that before I put this two 2 lines of code, it's working fine. It didn't give me any error messages or anythings. Can someone point me out what did I did wrong? This is two lines I put in: Code: [Select] $vuong_query = tep_db_query("select categories_id from " . TABLE_GENDER_CATEGORIES . " where parent_id = 1"); while ($product_check = tep_db_fetch_array($vuong_query)) { This is the whole piece of code: Code: [Select] <tr> <td><table width = "100%" border="1" bordercolor="maroon"> <tr> <?php $vuong_query = tep_db_query("select categories_id from " . TABLE_GENDER_CATEGORIES . " where parent_id = 1"); while ($product_check = tep_db_fetch_array($vuong_query)) { $categories_querys = tep_db_query("select c.categories_id, cd.categories_name, c.categories_image from " . TABLE_CATEGORIES . " c, " . TABLE_CATEGORIES_DESCRIPTION . " cd where c.categories_id = '" . $product_check['categories_id'] . "' and c.categories_id = cd.categories_id and cd.language_id = '" . (int)$languages_id . "' order by sort_order, cd.categories_name"); $number_of_categories = tep_db_num_rows($categories_querys); $rows = 0; while ($categories = tep_db_fetch_array($categories_querys)) { $rows++; $cPath_new = tep_get_path($categories['categories_id']); $width = (int)(100 / MAX_DISPLAY_CATEGORIES_PER_ROW) . '%'; echo ' <td align="center" class="smallText" width="' . $width . '" valign="top"><a href="' . tep_href_link(FILENAME_DEFAULT, $cPath_new) . '">' . tep_image(DIR_WS_IMAGES . $categories['categories_image'], $categories['categories_name'], SUBCATEGORY_IMAGE_WIDTH, SUBCATEGORY_IMAGE_HEIGHT) . '<br>' . $categories['categories_name'] . '</a></td>' . "\n"; if ((($rows / MAX_DISPLAY_CATEGORIES_PER_ROW) == floor($rows / MAX_DISPLAY_CATEGORIES_PER_ROW)) && ($rows != $number_of_categories)) { echo ' </tr>' . "\n"; echo ' <tr>' . "\n"; } } } ?> </tr> </table> </td> </tr> Thanks anyone for helping me out. Vanvoquan Hello, So I have a content management site set up, but I'm stuck on one part. I have a query. Code: [Select] Select `email` FROM author WHERE author.id IN(SELECT authorid FROM job WHERE id = '$_POST[id]') I'm a beginner in PHP and SQL. Basically I need the query to run, it will come out with one result and I need that to be where the email is sent to. This is the existing code I have for sending out the email $subject = " Job Application Confirmation"; $message = "Hello $_POST[name] , \r\rYou, or someone using your email address, has applied for the job: $text Using the Resource Locator at Prahan.com. Keep this as future reference. \r\r Full Name: $_POST[name] \r Location: $_POST[location] \r Email: $_POST[email] \r Additional Information: $_POST[info] \r Job ID: $id \r Job Name: $text \r \r Expect a response shortly. \r\r Regards, Prahan.com Team"; $headers = 'From: Prahan.com Team <noreply@prahan.com>'; 'Reply-To: noreply@prahan.com' . "\r\n" . 'X-Mailer: PHP/' . phpversion(); $to1 = 'pratik@prahan.com'; mail($to1, $result, $message, $headers); Thanks in Advance! Hi all !,
I am stuck on the following piece of code which does not give an error nor does it give a result. ( i.e. it gives 0 num_rows which should be > 1).
If, however, I execute the query in phpmyadmin by simply substituting the values of $pp,$ll and $room_no in the query it gives the correct result.
Please can someone tell me what I may be doing wrong here. Thanks !
$fcon = mysqli_connect($db_host,$db_user,$db_pass,$db_database) or die('Unable to establish a DB connection');
$pp = "(ms.level = 'Beginner' || ms.level = 'Intermediate')";
$ll = 'ms.diff <= 7';
$room_no = 4;
$query = "SELECT md.Member_reg_id, md.fname, md.lname, md.email, md.cell,
ms.level, ms.diff, ms.score,
r.ID_Status
FROM register as r
JOIN member_detail as md ON r.ID = md.Member_reg_id
JOIN memstatus as ms On r.ID = ms.ID
WHERE r.CENTERCODE = ? AND r.ID_Status ='A' AND ? AND ?
ORDER by level, diff, score DESC";
$stmt=$fcon->prepare($query);
$stmt->bind_param('iss',$room_no,$pp,$ll);
if(!$stmt->execute()) die('Failed to execute the query'.$fcon->error);
else
{
echo "Executed";
$stmt->bind_result($Member_reg_id,$fname,$lname,$email,$cell,$level,$diff ,$score,$ID_Status);
$numrows = $stmt->num_rows;
$stmt->store_result();
// echo $numrows;
while($stmt->fetch())
{
echo "<br>".$fname.' '.$lname;
echo "<br>".$level;
echo "<br>".$diff;
echo "<br>".$score;
echo "<br>".$cell;
echo "<br>".$email;
}
}
Edited by ajoo, 03 January 2015 - 08:00 AM. hello I'm using this code: Code: [Select] $query="SELECT * FROM `second_content` WHERE CHANGED =0 limit 0,1";to query first row data when I want see it and echo it I recived : Code: [Select] Resource id #2can anyone help me ? thank you Hello! I am busy developing a script that returns all users that have the cell "Available" set to 1 but I am unsure how to do this, I am familiar with basic SQL and I have made this: $sql = "SELECT * FROM `clans` WHERE `available` =1 LIMIT 0 , 30"; if ($result = mysql_query($sql)) { if (mysql_num_rows($result)) { $row = mysql_fetch_assoc($result); echo ($row['name']); but it only displays information from the first row found, and I want it to display all the rows found, how can I do this? Thanks - GreenFanta This topic has been moved to MySQL Help. http://www.phpfreaks.com/forums/index.php?topic=306966.0 Hi, I have attached sql script for table structure and sample data. When I run following sql query ,I get result set. Code: [Select] select * from tablename order by col10 desc I want to create array using result set. Following is the require structure of array. Code: [Select] <?php $resultSet[]=array ( ('0') =>array ( ('col10value1') => array ( ('0') => array ( ('col1') => 'col1', ('col2') => 'col2', ('col3') => 'col3', ('col4') => 'col4', ('col5') => 'col5', ('col6') => 'col6', ('col7') => 'col7', ('col8') => 'col8', ), ('1') => array ( ('col1') => 'col1', ('col2') => 'col2', ('col3') => 'col3', ('col4') => 'col4', ('col5') => 'col5', ('col6') => 'col6', ('col7') => 'col7', ('col8') => 'col8', ), ), ), ('1') => array ( ('col10value2') => array ( ('0') => array ( ('col1') => 'col1', ('col2') => 'col2', ('col3') => 'col3', ('col4') => 'col4', ('col5') => 'col5', ('col6') => 'col6', ('col7') => 'col7', ('col8') => 'col8', ), ), ), ); ?> Hello. This one is driving me up the wall. It used to work, I have a sports league database (MsSql) and one of the tables contains team Captain's first name, last name, etc.. To create a unique but simple login, the query searches for the captain's first initial of their first name + lastname + league. However since there is the remote possibility of two similar usernames (i.e. David Smith) if the last name is found, the query adds an integer to the end of the last name. So the result would look like this: DSmithM4A At the moment, my database is completely blank. However what is happening when I run the query below is I get: DSmith1M4A Can someone figure out why this code is not working correctly? If there is not more than one "Smith" no integer is supposed to be returned. There is something in the "Captain Count" that I think is not working correctly. Code: [Select] /* * Gather captian information */ $strCaptainFirstInitial = substr($arrPost['cpt_first'],0,1); $strCaptainLastName = preg_replace("/[^a-zA-Z]/","",$arrPost['cpt_last']); $strCaptainName = strtolower($strCaptainFirstInitial.$strCaptainLastName); $sqlCaptainSearch = sprintf ( $strCaptainSearch, $strCaptainName ); $resCaptainSearch = mssql_query($sqlCaptainSearch,$conDB); /* * Create unique captain login and Password */ $strCaptainCount = (mssql_num_rows($resCaptainSearch) > 0) ? mssql_num_rows($resCaptainSearch) : "" ; $strLeagueTypeInitial = substr($arrLeagueSearch['type'],0,1); $strCaptainLogin = $strCaptainName . $strCaptainCount . $strLeagueTypeInitial . $arrLeagueSearch['size'] . $arrLeagueSearch['division']; Hi all, Just a pretty basic question that I can't find the asnwer to: I have a MySQL query of the following form: Code: [Select] $result = mysql_query (" SELECT $cols FROM tablename WHERE......."); Where $cols is as follows: Code: [Select] $cols = firstname, lastname, $price1, $price2, etc. ($price1 and $price2 are variable column names that contains prices) How do I find the lowest value for $price1 and/or $price2 out of the results set of this query? Thanks! I have this query to show the current class I'm in: Code: [Select] $GetTimeTable = "SELECT * FROM timetable WHERE username='$username' AND Subject_date='$today_date'"; Then I have this to display current class: Code: [Select] if($TimeNow >= $Start_time && $TimeNow <= $End_time){ echo "Current Class: "; echo $Subject;} How would I find next class that day? Hello, I am new to the board and PHP. I created a simple store locator app with PHP. It pulls the info from a database. I want to take the website field from the table, once it is returned from the query, and have it print out a a hyperlink. Please help. Thanks, I have posted most of the relevant code. <?php if (isset ($stores)) { if (!empty ($stores)) { echo '<p><strong>' . count ($stores) . ' results were found.</strong></p>'; foreach ($stores as $value) { echo '<p><strong>' . $value['name'] . '</strong><br />'; echo $value['address'] . '<br />'; echo $value['town'] . ', ' . $value['state'] . ' ' . $value['postal']; echo ' <a target="_blank" href="http://maps.google.com/maps?q=', $value['address'], ' ', $value['town'], ', ', $value['state'], ' ', $value['postal'],'">Map this location</a><br />'; echo 'Phone: ' . $value['phone'] . '<br />'; echo 'Website: ' . $value['website'] . '<br />'; echo 'Email: ' . $value['email'] . '<br />'; echo 'Hours: ' . $value['hours'] ; echo '</p>'; } } else { echo '<p><strong>No results found</strong></p>'; } } ?> I´ve reached to
<?php require_once('Connections/conexxion.php'); ?>
<?php
mysql_select_db($database_conexxion, $conexxion);
$query_consulta = "SELECT venta,compra,taller,regula_mas,regula_menos,movimiento.id_item,sum(compra+regula_mas-venta-taller-regula_menos) as stock, cilindro, esfera FROM movimiento join item on item.id_item=movimiento.id_item join rx on rx.id_rx=item.id_rx join cilindro on cilindro.id_cil=rx.id_cil join esfera on esfera.id_esf=rx.id_esf GROUP BY movimiento.id_item ORDER BY esfera desc, cilindro desc";
$consulta = mysql_query($query_consulta, $conexxion) or die(mysql_error());
$row_consulta = mysql_fetch_assoc($consulta);
$totalRows_consulta = mysql_num_rows($consulta);
?>
<!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" />
<title>test</title>
</head>
<body>
<?php
echo "<table align=center>";
$columnes = 10; # Número de columnas (variable)
if (($rows=mysql_num_rows($consulta))==0) {
echo "<tr><td colspan=$columnes>No hay resultados en la BD.</td></tr> ";
} else {
echo "<tr><td colspan=$columnes>$rows Resultados </td></tr>";
}
for ($i=1; $row = mysql_fetch_row ($consulta); $i++) {
$resto = ($i % $columnes); # Número de celda del <tr> en que nos encontramos
if ($resto == 1) {echo "<tr>";} # Si es la primera celda, abrimos <tr>
echo "<td>$row[1]</td>";
if ($resto == 0) {echo "</tr>";} # Si es la última celda, cerramos </tr>
}
if ($resto <> 0) { # Si el resultado no es múltiple de $columnes acabamos de rellenar los huecos
$ajust = $columnes - $resto; # Número de huecos necesarios
for ($j = 0; $j < $ajust; $j++) {echo "<td> </td>";}
echo "</tr>"; # Cerramos la última línea </tr>
}
mysql_close($connexion);
echo "</table>";
?>
</body>
</html>
<?php
mysql_free_result($consulta);
?>
it "works", but results doesn´t start at number 1 but number 2
anyways, The intent of this table is to show the stock of a product, a lens, that has a range of Rx, we use to see this range this way, and show the stock this way would be very productive, but I can´t figure out how to achive it, any sugestion??
Edited by gralfitox, 16 December 2014 - 06:45 AM. This topic has been moved to CSS Help. http://www.phpfreaks.com/forums/index.php?topic=353608.0 Hello - Suppose I had a MySQL result set that was something like this: ITEM: COLOR: ball red ball blue book red book green book black If I were to use the code: Code: [Select] do { echo $row_myQuery['item'] . " - " . echo $row_myQuery['color'] . "<br />"; } while ($row_myQuery= mysql_fetch_assoc($myQuery)); Then I end up with this: Code: [Select] ball - red ball - blue book - red book - green book - black MY QUESTION: What I'd like to know is, how could I re-write that php code so that I could get an output like this?: Code: [Select] ball - red, blue book - red, green, black Thanks! |
|||||||