|
PHP - Php/mysql Select And Display Result While Array And Integer Increment
Hi, I have come up with the following code, I need it to get the details of several scattered products and echo the results, the trick is I don't want it to echo the results one after the other... I want to have the products scattered between unique text on the page but don't want to run the query several times for performance reasons.
E.g.- PAGE to look like this: $Product_1 unique text/images $Product_2 $Product_3 unique text/images $Product_4 Current Code: Code: [Select] <? $result = mysql_query("SELECT * FROM products where Product_ID IN (475, 465, 234, 567, 845)"); while($row = mysql_fetch_array($result)) { $x = "1"; while ($x<=3) { echo $x; $Product = "Product_"; $Product = $Product.$x; echo $Product; $Product = $row['Product_ID']; echo $Product; $x++; echo $x; } } At the moment it returns the following results: Quote 1 Product_1 465 2 2 Product_2 465 3 3 Product_3 465 4 1 Product_1 475 2 2 Product_2 475 3 3 Product_3 475 4 A few problems... In Blue... it duplicates for product 465 In Red... It repeats again for 475 Also.... it starts with 465, but I want it to go in order as how it appears - $result = mysql_query("SELECT * FROM products where Product_ID IN (475, 465, 234, 567, 845)"); so should start with 475 I want to get the following result: Quote 1 Product_1 475 2 2 Product_2 465 3 3 Product_3 234 4 4 Product_4 567 4 (and so on.....) If anyone could provide me assistance with my troubled 'while loop' statement that would be much appreciated! Similar TutorialsOkay so, I'm adding a reply feature to my text-board, and I'm wondering how I can select a single result from the DB I've tried this $board = mysql_real_escape_string($_GET['board']); $thread = mysql_real_escape_string($_GET['thread']); $result = mysql_query("SELECT subject, name, id, timestamp, body FROM $board LIMIT $thread,$thread"); while ($row = mysql_fetch_array($result, MYSQL_NUM)) { printf("<div id='html1' style='background-color:#F0E0D6;border:solid 1px #800000;font-family:arial; font-size: 12px; padding:2px; margin:2px;'><font color='#CC1105'>%s </font><font color='#117743'>%s</font> <font color='#800000'>Post # %s - %s<br />%s<br /></div>", $row[0], $row[1], $row[2], $row[3], $row[4], $row[5], $row[6]); mysql_free_result($result); } ?> Kinda works but if I pu ?thread=56 then it would show thread 57, which in this example does not exist. I am trying to update a mysql table called AvItems with the value 'Torso' in the Equip "section?" I have been through the forums and cannot see anything to match. I dont mind if the page looses the onsubmit() and has a button instead. Though I would like to update the database and link back to the same page: There is a display that shows the item that is currently equiped, I have put this in to show it works, or doesn't as the case may be. Hope I got the code /code right this time. many thanks in advance Andy Curtis Code: [Select] create table Items( ItemID integer unsigned auto_increment primary key, ItemName varchar(20) not null, Type varchar(10), UsedOn varchar(10), ); create table AvItems( AvItemID integer unsigned auto_increment primary key, AvID integer unsigned, ItemID integer unsigned, Equip varchar(8)); <?php $username="root"; $password="MyPassword"; $database="MyDataBase"; $AvName = "AndyJCurtis"; mysql_connect(localhost,$username,$password); @mysql_select_db($database) or die( "Unable to select database"); $AvAccR = mysql_query( " SELECT AvID FROM AvAcc WHERE AvName = '$AvName' " ); $AvID = mysql_result($AvAccR, 0, 'AvID'); /////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// $Torso = mysql_query(" select ItemName from AvItems, Items where AvItems.itemID = Items.itemID AND AvItems.AvID = '$AvID' AND UsedON = 'Body' "); $TorsoE = mysql_query(" select ItemName from AvItems, Items where AvItems.itemID = Items.itemID AND AvItems.AvID = '$AvID' And UsedON = 'Body' AND Equip = 'Body' "); if(mysql_num_rows($TorsoE) != 0) { $TorsoItem = mysql_result($TorsoE ,0,"ItemName"); //mysql_close(); ?> <title></title> <head></head> <body> <form action="http://localhost/CI/Equip2.php" method="post"> <table border=1> <tbody> <tr> <td>Torso<BR> <?PHP echo "$TorsoItem <BR>"; ?> <select name="Torso" onchange="submit();" value =" Update"> <?PHP while($TorsoRow = mysql_fetch_array($Torso)) { echo "<option value=\"".$TorsoRow['ItemName']."\">".$TorsoRow['ItemName']."\n </option>"; } ?> </select> </td> </tr> //////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// <?php if($_POST['Torso'] == 'Update') { mysql_query("update AvItems set Equip = '' where Equip='Torso'") or die("cant update unequip"); mysql_query("update AvItems set Equip = 'Torso' where ItemID='{$_POST['ItemName']}'") or die("cant update equip"); } ?> /////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// </tbody> </table> </.form> </body> </html> Hey guys. Me again... Essentially what i am doing is pulling data from a MySQL database about the number of thumbnails on a page. The user can then change this using a <select> dropdown menu. How ever, i want the <select> to default to the amount already specified by the Database. I know i can do this by inserting a Selected attribute to one of the <options> but what is the best way of doing this? Heres my code.. $NumberOfThumbnails = mysql_result($data, 0,"NumberOfThumbnails"); <select name="numberofthumbnails"> <option value="0">None</option> <option value="2">2</option> <option value="4">4</option> <option value="6">6</option> <option value="8">8</option> <option value="10">10</option> <option value="12">12</option> <option value="14">14</option> <option value="16">16</option> <option value="18">18</option> <option value="20">20</option> <option value="22">22</option> <option value="24">24</option> <option value="25">25</option> <option value="26">26</option> <option value="27">27</option> <option value="28">28</option> </select> Thanks - Danny When I run 'select 1700-price as blah from goldclose as t2 order by dayid desc limit 1' by itself in mysql I get a numerical result: one row, one column. In my php script, the 1700 is actually a variable. so here it is $changequery = sprintf("select $goldprice-price as change from goldclose order by dayid desc limit 1"); $change = mysql_query(changequery); while ($row = mysql_fetch_array($change)) { printf("$row[0]"); } mysql_free_result($changeresult); I get the following error, Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in <b>/root/fuzzy/htmlmain4.php on line 99 Warning: mysql_free_result(): supplied argument is not a valid MySQL result resource in <b>/root/fuzzy/htmlmain4.php on line 103 Not sure why? All i want is to get the result of that select statement into a variable such as $change I am seeking to learn more about the noted subject, how to use PHP to allow a user to enter search terms and search a database. I have experimented with this with little results save for errors. Please see code listed below: search.php <? //// filename = search.php <form method="post" action="result.php3"> <select name="metode" size="1"> <option value="row_name1">metode1</option> <option value="row_name2">metode2</option> </select> <input type="text" name="search" size="25"> <input type="submit" value="Begin Searching!!"> </form> ?> results.php //// filename = result.php3 <? $hostname = "mysql7.000webhost.com"; // Usually localhost. $username = "a4542527_root"; // If you have no username, leave this space empty. $password = "*******"; // The same applies here. $usertable = "people"; // This is the table you made. $dbName = "a4542527_test1"; // This is the main database you connect to. MYSQL_CONNECT($hostname, $username, $password) OR DIE("Unable to connect to database"); @mysql_select_db( "$dbName") or die( "Unable to select database"); ?> <? //error message (not found message) $XX = "No Record Found"; $query = mysql_query("SELECT * FROM $usertable WHERE $metode LIKE '%$search%' LIMIT 0, 30 "); while ($row = mysql_fetch_array($query)) { $variable1=$row["row_name1"]; $variable2=$row["row_name2"]; $variable3=$row["row_name3"]; print ("this is for $variable1, and this print the variable2 end so on..."); } //below this is the function for no record!! if (!$variable1) { print ("$XX"); } //end ?> Upon viewing search.php I receive the error message: Parse error: syntax error, unexpected '<' in /home/a4542527/public_html/search.php on line 3 I believe I may be missing something and am a bit lost. Thank-you in in advance for any help or suggestions. ~Matty Hello to all. I'm new here and i am very glad to be a part of this community. I am new in PHP/mysql but i try to learn as much as i can in php & mysql. And here is the problem: Into a php or html page i want to display 2 dropdown list to act as a filter for displaying a table. I managed to display a table based on option select from first dropdown but i don'y know how to make or activate the second dropdown list to act as a filter, and when i press "result" to display on the same page the results. Thanks a lot. I had this working, but when I try and get fancy and use AJAX the data doesn't display. I think this is a PHP problem though. My code for the select form including AJAX code Code: [Select] <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd"> <html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en-GB"> <head> <title>AJAX Example</title> <link rel="stylesheet" type="text/css" href="Form.css" media="screen" /> <script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.4.2/jquery.min.js"></script> <script type="text/javascript"> $(document).ready(function(){ $("tr:odd").addClass("odd"); }); </script> <script type="text/javascript"> function showPlayers(str) { var xmlhttp; if (str=="") { document.getElementById("DataDisplay").innerHTML=""; return; } if (window.XMLHttpRequest) {// code for IE7+, Firefox, Chrome, Opera, Safari xmlhttp=new XMLHttpRequest(); else {// code for IE6, IE5 } xmlhttp=new ActiveXObject("Microsoft.XMLHTTP"); } xmlhttp.onreadystatechange=function() { if (xmlhttp.readyState==4 && xmlhttp.status==200) { document.getElementById("DataDisplay").innerHTML=xmlhttp.responseText; } } xmlhttp.open("GET","Query.php?category_id="+str,true); xmlhttp.send(); } </script> </head> <body> <h1">AJAX Example</h1> <?php #connect to MySQL $conn = @mysql_connect( "localhost","username","pw") or die( "You did not successfully connect to the DB!" ); #select the specified database $rs = @mysql_SELECT_DB ("MyDB", $conn ) or die ( "Error connecting to the database test!"); ?> <form name="sports" id="sports"> <legend>Select a Sport</legend> <select name="category_id" onChange="showPlayers(this.value)"> <option value="">Select a Sport:</option> <?php $sql = "SELECT category_id, sport FROM sports ". "ORDER BY sport"; $rs = mysql_query($sql); while($row = mysql_fetch_array($rs)) { echo "<option value=\"".$row['category_id']."\">".$row['sport']."</option>\n "; } ?> </select> </form> <br /> <div id="DataDisplay"></div> </body> </html> Query.php <?php #get the id $id=$_GET["category_id"]; #connect to MySQL $conn = @mysql_connect( "localhost","username","pw") or die( "Error connecting to MySQL" ); #select the specified database $rs = @mysql_SELECT_DB ("MyDB", $conn ) or die ( "Could not select that particular Database"); #$id="category_id"; #create the query $sql ="SELECT * FROM sports INNER JOIN players ON sports.category_id = players.category_id WHERE players.category_id = '".$id."'"; echo $sql; #execute the query $rs = mysql_query($sql,$conn); #start the table code echo "<table><tr><th>Category ID</th><th>Sport</th><th>First Name</th><th>Last Name</th></tr>"; #write the data while( $row = mysql_fetch_array( $rs) ) { echo ("<tr><td>"); echo ($row["category_id"] ); echo ("</td>"); echo ("<td>"); echo ($row["sport"]); echo ("</td>"); echo ("<td>"); echo ($row["first_name"]); echo ("</td>"); echo ("<td>"); echo ($row["last_name"]); echo ("</td></tr>"); } echo "</tr></table>"; mysql_close($conn); ?> I think the problem is either with this part in the AJAX Code: [Select] xmlhttp.open("GET","Query.php?category_id="+str,true); or most likely in my Query.php code when I wasn't using AJAX and using POST it worked fine, but adding the AJAX stuff and GET it doesn't work. When I echo out the SQL the result is Code: [Select] SELECT * FROM sports INNER JOIN players ON sports.category_id = players.category_id WHERE players.category_id = '' so the category_id is not being selected properly and that is the primary key/foreign key in the MySQL table which connects the JOIN. I am working on a project where I want a select form to display information from a MySQL table. The select values will be different sports (basketball,baseball,hockey,football) and the display will be various players from those sports. I have set up so far two tables in MySQL. One is called 'sports' and contains two columns. Once called 'category_id' and that is the primary key and auto increments. The other column is 'sports' and contains the various sports I mentioned. For my select menu I created the following code. <?php #connect to MySQL $conn = @mysql_connect( "localhost","uname","pw") or die( "You did not successfully connect to the DB!" ); #select the specified database $rs = @mysql_SELECT_DB ("test", $conn ) or die ( "Error connecting to the database test!"); ?> <html> <head>Display MySQL</head> <body> <form name="form2" id="form2"action="" > <select name="categoryID"> <?php $sql = "SELECT category_id, sport FROM sports ". "ORDER BY sport"; $rs = mysql_query($sql); while($row = mysql_fetch_array($rs)) { echo "<option value=\"".$row['category_id']."\">".$row['sport']."</option>\n "; } ?> </select> </form> </body> </html> this works great. I also created another table called 'players' which contains the fields 'player_id' which is the primary key and auto increments, category_id' which is the foreign key for the sports table, sport, first_name, last_name. The code I am using the query and display the desired result is as follows <html> <head> <title>Get MySQL Data</title> </head> <body> <?php #connect to MySQL $conn = @mysql_connect( "localhost","uname","pw") or die( "Err:Db" ); #select the specified database $rs = @mysql_SELECT_DB ("test", $conn ) or die ( "Err:Db"); #create the query $sql ="SELECT * FROM sports INNER JOIN players ON sports.category_id = players.category_id WHERE players.sport = 'Basketball'"; #execute the query $rs = mysql_query($sql,$conn); #write the data while( $row = mysql_fetch_array( $rs) ) { echo ("<table border='1'><tr><td>"); echo ("Caetegory ID: " . $row["category_id"] ); echo ("</td>"); echo ("<td>"); echo ( "Sport: " .$row["sport"]); echo ("</td>"); echo ("<td>"); echo ( "first_name: " .$row["first_name"]); echo ("</td>"); echo ("<td>"); echo ( "last_name: " .$row["last_name"]); echo ("</td>"); echo ("</tr></table>"); } ?> </body> </html> this also works fine. All I need to do is tie the two together so that when a particular sport is selected, the query will display below in a table. I know I need to change my WHERE clause to a variable. This is what I need help with. thanks This topic has been moved to MySQL Help. http://www.phpfreaks.com/forums/index.php?topic=321437.0 Here is my query: Code: [Select] $db->query("SELECT cache.*,u.username from search_cache cache LEFT Join users as u ON u.id = 'NEED HELP HERE' where ident = '{$username}' ORDER BY DATE DESC") or error('Unable to send the message.', __FILE__, __LINE__, $db->error()); Here is my data inside my search_cache Here is my while loop: Code: [Select] while($recent = $db->fetch_assoc($rec)){ $data = unserialize($recent['search_data']); } Now I can use $data['search_type'] as a array and If I use echo $data['search_type']['2'] it echo's out the user_id 32 as seen in the screenshot at the very end. Problem is, I want to use that 'NEED HELP HERE' to join with that id 32 dynamically, how the hell is that possible with mysql or is it? Or would it be easier to just add a new row/column named user_id and just join of that instead of trying to do all this? would that be faster? Hi guys I'm having problem on how to display my php mysql result like this.. Digitalpoint, phpfreaks, adsense here's my code Code: [Select] $result = mysql_query ("SELECT dateline, dateline_from, eventid, title FROM vb_event WHERE calendarid = 1 AND dateline_from >= '$day_before' ORDER BY dateline_from LIMIT 2"); while($row = mysql_fetch_array( $result )) { echo "<a href='/forum/calendar.php?do=getinfo&e=" . $row['eventid'] . "&c=1' title='". $row['title'] . "' style='font-size:12px;'>" . $row['title'] . "</a> " ; } the result is Digitalpoint, phpfreaks, adsense, how to remove the "," in the end? it should be like this.. Digitalpoint, phpfreaks, adsense with no , comma in the end.. Thank you.. I am simply trying to insert a value generated from an array in a while loop, but it seems the value is not global and I can't pass it as I like. I did some research on this, but could not find an answer that solved my issue... Here is my select box code which is working perfect: <select name="city"> <?php $sql = "SELECT id, city_name FROM cities ". "ORDER BY city_name"; $results_set = (mysqli_query($cxn, $sql)) or die("Was not able to produce the result set!"); while($row = mysqli_fetch_array($results_set)) { echo "<option value=$row[id]>$row[city_name]</option>"; } ?> </select> Are any variables defined in a while loop global to the while loop only? Here is my SQL which you can see my $row[id] being passed thru field city_id... The value is being generated as supposed to based on value like: value="1", value="2" etc.. for the select options for each city name. So the values are there... But I CANNOT get that numerical id to pass to the database when submitting my form. Any ideas for a workaround to get this value passing as normal? if (isset($_POST['addPosting'])) { $query = "INSERT INTO Postings (id, city_id, title, description) VALUES ('','$row[id]','$_POST[title]','$_POST[description]')"; hi .. i am working on chat system ..there are two types of user one are register and other are guest and i have to add guest user..in to database as temporary so they can chat with each other the problem i am facing is with there names i want name like this (Guest1,Guest2,Guest3,Guest4,...so on..)..i am trying but it is not working on live server .. i have tested it on my local server using xampp server.. it worked but not on live server... here is the code which i am using if(isset($_SESSION['Guest'])) { $_SESSION['Guest']=$_SESSION['Guest']+1; } else { $_SESSION['Guest']=1; and one more thing how i can delete Guest users who are not online.. ok this may not be able to be done .... but here goes nothing ... i have a roster that displays member names as a link to thier profile using a form and onclick submit() now what i didnt realize is that the form name will have to increment for each record returned from the query. i am by no means a guru with php so i need some help adding the foreach statement and setting the form name to increment each time what i need: I need this to automatically go +1 for each record returned Code: [Select] <form name="view3" action="./viewinfo.php" method="post"> <a href="#" onclick="document['view3'].submit()"> and i need the correct way to use the foreach statement ... i have read the manual and it just confuzzeled me . here is what i have so far. <?php include './clan_new/config.php'; include './clan_new/access.php'; $db = mysql_connect ($hostname, $username, $password) or die ('Failed to connect to database: ' . mysql_error()); mysql_select_db($database); $query = "SELECT * FROM $member_table WHERE $member_table.rank = 6 ORDER BY $member_table.name ASC"; $result = mysql_query($query) or die ('Failed to query ' . mysql_error()); while ($row = mysql_fetch_assoc($result)) { $steamid = $row['authid']; $name = $row['name']; $email = $row['email']; $fid = $row['fid']; $avatar = $row['avatar']; echo "<tr>"; if ($avatar==""){ echo "<td> </td>"; } else{ echo "<td><img height='35' width='35' src='./clan_new/avatars/$avatar'></td>"; } ?> <form name="view3" action="./viewinfo.php" method="post"> <input type="hidden" id="authid" name="authid" value="<?php echo "$steamid" ; ?>" /> </form> <td align="center"><a href="#" onclick="document['view3'].submit()"> <?php echo "$name" ; ?></a></td> <?php echo "<td align=\"center\"><a class=\"style2\" href=\"mailto:$email\" class=\"style2\">$email</a></td>"; if ($fid==""){ echo ""; } else{ echo "<td align=\"center\">+<a class=\"style2\" href=\"steam://friends/add/$fid\">Friend</a></td>"; } echo "</tr>"; } mysql_free_result($result); mysql_close($db); ?> Hi there, I am working on an associative array: The array is: $currentIds //when we do print_r($currentIds); //it returns value 41 and 42. Array ( [0] => [1] => 41 [2] => 42 [3] => ) Which is OK but I have to use these two values in an SQL query but they are in Array forms. I need to convert them to integer to be able to use them in SQL query. Please let me know how I can convert this Array to Integer so that i can use the integer values for my SQL statement. Thank you All comments and feedback are always welcomed Cheers! Hey, Im trying make a way to check an input to make sure it only consists of "1,2,3,4,5,6,7,8,9,0 and . " I thought i could explode the number and check it to an array that has "1,2,3,4,5,6,7,8,9,0 and . " I havent worked with arrays before so i dont quite know how they work or to go about this? Any ideas cause im sure im not the first to ask for something like this Thanks Hi- I did this and it works... based on the date, the values in the array (from 1 to 9) are echoed by calling $num. Code: [Select] <?php $start = "2012-02-27"; $now = date("Y-m-d"); $week2 = strtotime(date("Y-m-d", strtotime($start)) . " +1 week"); $week2_st = date("Y-m-d", $week2); $week3 = strtotime(date("Y-m-d", strtotime($start)) . " +2 weeks"); $week3_st = date("Y-m-d", $week3); $week4 = strtotime(date("Y-m-d", strtotime($start)) . " +3 weeks"); $week4_st = date("Y-m-d", $week4); $week5 = strtotime(date("Y-m-d", strtotime($start)) . " +4 weeks"); $week5_st = date("Y-m-d", $week5); $week6 = strtotime(date("Y-m-d", strtotime($start)) . " +5 weeks"); $week6_st = date("Y-m-d", $week6); $week7 = strtotime(date("Y-m-d", strtotime($start)) . " +6 weeks"); $week7_st = date("Y-m-d", $week7); $week8 = strtotime(date("Y-m-d", strtotime($start)) . " +7 weeks"); $week8_st = date("Y-m-d", $week8); $week9 = strtotime(date("Y-m-d", strtotime($start)) . " +8 weeks"); $week9_st = date("Y-m-d", $week9); $var_name = array('1', '2','3','4,'5','6','7,'8','9'); if ($now <= $week2_st){ $num = $var_name[0];} elseif($now >= $week2_st && $now <= $week3_st){ $num = $var_name[1];} elseif($now >= $week3_st && $now <= $week4_st){ $num = $var_name[2];} elseif($now >= $week4_st && $now <= $week5_st){ $num = $var_name[3];} elseif($now >= $week5_st && $now <= $week6_st){ $num = $var_name[4];} elseif($now >= $week6_st && $now <= $week7_st){ $num = $var_name[5];} elseif($now >= $week7_st && $now <= $week8_st){ $num = $var_name[6];} elseif($now >= $week8_st && $now <= $week9_st){ $num = $var_name[7];} elseif($now >= $week9_st){ $num = $var_name[8];} else{ $num :'( = $var_name[0];} ?> I'm trying to do a for loop so the index # increments by one and i only call $r instead $var_name[0], $var_name[1], $var_name[2], and so one. Any ideas? Code: [Select] $count = count($var_name); for ($i = 0; $i < $count; $i++) { $r = $var_name[$i]; } I want to perform a query which returns a subset of the fields in a table. One particular mySQL field is VARCHAR I have a query like this: $query = mysql_query("SELECT * FROM table WHERE code LIKE '3%') ; It's my understanding this should return all values which begin with "3", but it only returns about a dozen of the values 3, 30-39, 300-399, etc. (It works with string fields, but this field contains numerals.) Any help appreciated. thanks, Tom |
|||||||