|
PHP - How To Select Question From Mysql Tables Randomly ?
I am working on a quiz app
image 1 shows the index.php page image 2 shows the first question image 3 shows the second question image 4 shows the third question image 5 shows the result after completing the quiz image 6 shows the database 'quizzer' and its tables image 7 shows the 'questions' table image 8 shows the 'choices' table THIS LINK CONTAIN ALL THE CODE (and images) I HAVE DONE SO FAR https://www.mediafir...o7f5q0fe6y/quiz 1.Now my question is how to select the question RANDOMLY from 'questions' table along with 'choices' (by adding code to the existing file or create a new one). 2.If user refresh/reload the page before starting ('Start Quiz') or click 'Take Again' after finishing the quiz, the question should appear randomly. 3.Basically I want to change the order of question appearing in the browser each time I refresh. 4.My work so far is mentioned above.........Please help me with this "RANDOM" problem !! P.S - Will it be possible, by creating a random function in PHP which will check for repeat questions in a session and check for the 'id' of the question and if it is new display it on the page. If so what should I do and if no then how to do? Similar TutorialsI 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 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 Hi, I have an array of items and I randomly want to remove one from the array. What's the easiest way to go about this? Say I have two variables like below. I basically want to select one of them, but through random choice. How do I do that? $orange = 'Orange'; $apple = 'Apple';
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 Hi All,
I'm looking to update a mysql column weekly, where by 5 randomly selected rows are given a new random number, from between 1 and the num_rows / 5. This cycle is looped again until all the entries have a new random group for the week. It is to help mix up the players for teams for 5 aside. It needs to be able to accomodate the posibillity of the num_rows not dividing exactly by 5, using ceil perhaps?
As an example
Name Group
a 1
b 2
c 1
d 1
e 1
f 3
g 2
h 1
i 2
j 2
k 2
Then Next week
Name Group
a 2
b 1
c 2
d 2
e 1
f 2
g 3
h 1
i 2
j 1
k 1
I hope that makes sense. It'll be automated via cron job.
I've been really struggling with it, this is as far as I have got.
$totalgroupsraw = $num_rows /5; This portion is kind of stumping me. Basically, I have a two tables in this DB: users and users_access_level (Separated for DB normalization) users: id / username / password / realname / access_level users_access_level: access_level / access_name What I'm trying to do, is echo the data onto an HTML table that displays users.username in one table data and then uses the users.access_level to find users_access_level.access_name and echo into the following table data, I would prefer not to use multiple queries if possible or nested queries. Example row for users: 1234 / tmac / password / tmac / 99 Example row for users_access_level: 99 / Admin Using the examples above, I would want the output to appear as such: Username: Access Name: Tmac Admin I am not 100% sure where to start with this, but I pick up quickly, I just need a nudge in the right direction. The code I attempted to create just shows my lack of knowledge of joining tables, but I'll post it if you want to see that I did at least make an effort to code this myself. Thanks for reading! I have 2 queries that I want to join together to make one row
hey, i trying to select * colums form one table and 1 colum from another table... this is my code: Code: [Select] $DB->query("SELECT m.*, v.time, v.viewerid FROM members m LEFT JOIN profile_views v ON (m.id=v.userid) WHERE m.id IN ({$viewers_ids})"); but sometimes this query return multiply rows... any ideas? Thanks! hey, i'm trying to select information from 2 tables in one query... so i have 2 tables... members - (holds the members information) looks like this: Code: [Select] +---+--------+------------+------------+ | id | name | email | password | +---+--------+------------+------------+ | 1 | user1 | 1@g.com | **** | | 2 | user2 | 2@g.com | **** | | 3 | user3 | 3@g.com | **** | | 4 | user4 | 4@g.com | **** | | 5 | user5 | 5@g.com | **** | | 6 | user6 | 6@g.com | **** | +---+--------+------------+------------+ the second table is profile_views which stores the profile views... Code: [Select] +---+--------+-----------+---------------+ | id | userid | viewerid | time | +---+--------+-----------+---------------+ | 1 | 4 | 1 | 1287949172 | | 2 | 6 | 2 | 1287949172 | | 3 | 2 | 4 | 1287949172 | | 4 | 4 | 5 | 1287949172 | | 5 | 3 | 2 | 1287949172 | +---+--------+-----------+---------------+ userid - the profile (member) id that been watched viewerid - the id of the member who watched time - the time this happened im trying to select from profile_views the viewers ids' and select the info from the members according to the viewerid, but also i want to select the time from the profile_views, so it's should look like this: Code: [Select] +---+--------+------------+----------+------------+ | id | name | email | password | time +---+--------+------------+----------+------------+ | 1 | user1 | 1@g.com | **** | 1287949172 | 2 | user2 | 2@g.com | **** | 1287949172 | 3 | user3 | 3@g.com | **** | 1287949172 | 4 | user4 | 4@g.com | **** | 1287949172 | 5 | user5 | 5@g.com | **** | 1287949172 | 6 | user6 | 6@g.com | **** | 1287949172 +---+--------+------------+----------+------------+ this is my code: Code: [Select] $DB->query("SELECT viewerid FROM profile_views WHERE userid={$core->input['showuser']} ORDER BY time DESC LIMIT 5"); if ( $DB->get_num_rows() > 0 ) { while ( $all_viewers = $DB->fetch_row() ) { $viewers_ids[] = $all_viewers['viewerid']; } $viewers_ids = implode(",", $viewers_ids); $DB->query("SELECT m.*, v.* FROM members m LEFT JOIN profile_views v ON (v.userid=m.id) WHERE m.id IN ({$viewers_ids})"); while ( $viewers = $DB->fetch_row() ) { } } but it's just dont work! any ideas? Thanks in advance! Hi there I have 2 tables: Fleet FleetName PlanetName Status Planet PlanetName PlayerName Im trying to write a select query that will search through the fleet table and display Fleet table details Where the PlanetName from the fleet table is = to the PlanetName of the Planet table. Ive tried joining the tables but i dont think the logic quite works.. Code: [Select] mysql_select_db($database_swb, $swb); $query_Fleet = sprintf("SELECT fleet.FleetName, planet.PlanetName FROM fleet, planet WHERE fleet.PlanetName = planet.PlanetName"); $Fleet = mysql_query($query_Fleet, $swb) or die(mysql_error()); $row_Fleet = mysql_fetch_assoc($Fleet); $totalRows_Fleet = mysql_num_rows($Fleet); I want ONLY the Fleets from the planet i am looking at to be displayed not all of them. I have created another query that displays the planet name of the particular one I am looking at as it is parsed from a hyperlink on a previous page. So is there some way i can use both queries together? "SELECT FROM fleet, WHERE $fleet.PlanetName = $planet.PlanetName" Im a little bit confussed, please help I'm having a hard time grasping how to select data from several tables. Here's the code Code: [Select] <?php require_once "config.php"; $gamedate = $_SESSION['date']; echo "These umpires are not currently scheduled on this date, and have not asked for the day off:<br />"; $umpirelist = Array('umpire 1 name', 'umpire 2 name', 'umpire 3 name'); $dbc = mysql_pconnect($host, $username, $password); mysql_select_db($db,$dbc); foreach($umpirelist as $data) { //now get stuff from a table $sql = "SELECT calendar.date, calendar.ump1, calendar.ump2, calendar.ump3, calendar.ump4, calendar.ump5, vacation.date, vacation.umpire FROM umps, calendar, vacation WHERE umps.full_name = $data AND $gamedate = `calendar.date` AND $gamedate = `vacation.date` AND $data NOT IN ('calendar.ump1', 'calendar.ump2', 'calendar.ump3', 'calendar.ump4', 'calendar.ump5') AND $data NOT IN ('vacation.umpire') order by umps.full_name asc"; $rs = mysql_query($sql,$dbc); $matches = 0; while ($row = mysql_fetch_assoc($rs)) { $matches++; echo "$row[$data]<br />"; } } ?> The table CALENDAR holds the date of the game, who is playing, and what umpires are schedule for that game. it does have a unique "row_number" column as well, but didn't think I'd need it in this select. The table VACATION has the date, and umpire's name that is on vacation. The table UMPS contains the umpires name (same names as the array in the code) and email addresses and login info. I'm getting this error: Quote Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource on line 152 This is line 152 Quote while ($row = mysql_fetch_assoc($rs)) { Can someone please point me in the right direction as to where I'm going wrong? Thanks! Hi guys, This is my first time posting here - im just getting into PHP - i got a question; I have two databases: profile(id, name, interests, dob, gender, join_date, email) interests(id, profile_id, interests) id being the primary key, and profile_id being the foreign key from profile. I want to script that returns profile information and all the matching interests (one user can have multiple interests). This is what i have so far, though it does not work, and i knew it wouldnt; function get_profile($id) { $connection = mysql_open(); $query = "SELECT * "; $query .= "FROM profiles, interests "; $query .= "WHERE profile.id=" . $id; $query .= " AND interests.profile_id=" . $id; $result = @ mysql_query($query, $connection); // Transform the result set to an array (for Smarty) $entries = array(); while ($row = mysql_fetch_array($result)) { $entries[] = $row; } mysql_close($connection) or show_error();; return $entries; } Can someone please advise on how this can be done? or do i need to have two query's one for each table ? Thank you in advance!! Hi ho, I am trying to assemble a table so that it shows article in order by id descending. The current one i am using is working but not displaying as i hoped, i need them aligned on the left and right, so id=1 align left, id=2 align right etc. Here's my current code Code: [Select] <table style='table-layout:fixed' align="center" cellpadding="2" cellspacing="0" colspan="" class="Main"><br> <? $select_paper=mysql_query("SELECT * FROM paper ORDER BY id DESC"); while($the=mysql_fetch_object($select_paper)){ ?> <tr> <td class="subtableheader" width="25%"><?php echo "$the->title"; ?></td> </tr> <tr> <td class="profilerow" width="100%"><?php echo "$the->news"; ?></td> </tr> <tr> <td class="subtableheader" width="25%"><?php echo "Article By $the->by - $the->date"; ?></td> </tr> hi im trying to echo out all the possibilities from one column in 2 different tables, both with the same structure. if that makes sense this is what i have $query = mysql_query("SELECT * FROM stanjamesprem,centrebetprem GROUP BY eventname ORDER BY eventtime "); while($row = mysql_fetch_assoc($query)) { echo $row[eventname]; } if i take one of the tables out it works fine but if i have 2 table names it doenst work please help me cheers matt any questions please ask away This topic is now in MySQL Help. http://www.phpfreaks.com/forums/index.php?topic=357554.0 I want to select the user from super_administrators, administrators, teachers and students, and give the user permission based from what table he "came". But is giving the "Query failed" error... Code: [Select] <?php //Start session session_start(); //Include database connection details require_once('../config/config.php'); //Array to store validation errors $errmsg_arr = array(); //Validation error flag $errflag = false; //Connect to mysql server $link = mysql_connect(DB_HOST, DB_USER, DB_PASSWORD); if(!$link) { die('Failed to connect to server: ' . mysql_error()); } //Select database $db = mysql_select_db(DB_DATABASE); if(!$db) { die("Unable to select database"); } //Function to sanitize values received from the form. Prevents SQL injection function clean($str) { $str = @trim($str); if(get_magic_quotes_gpc()) { $str = stripslashes($str); } return mysql_real_escape_string($str); } //Sanitize the POST values $email = clean($_POST['email']); $password = clean($_POST['password']); //Input Validations if($email == '') { $errmsg_arr[] = 'O campo Email nao foi preenchido.'; $errflag = true; } if($password == '') { $errmsg_arr[] = 'O campo Senha nao foi preenchido.'; $errflag = true; } //If there are input validations, redirect back to the login form if($errflag) { $_SESSION['ERRMSG_ARR'] = $errmsg_arr; session_write_close(); header("location: ../index.php"); exit(); } //Create query $qry = "SELECT * FROM super_administrators,administrators,teachers,students WHERE email = '$email' AND passwd = '".md5($_POST['password'])."'"; $result = mysql_query($qry); $member = mysql_fetch_assoc($result); $table = mysql_field_table('$result', '0'); //Check whether the query was successful or not if($result) { if(mysql_num_rows($result) == 1) { if($table == 'super_administrators') { session_regenerate_id(); $_SESSION['SESS_ID'] = $member['id']; $_SESSION['SESS_NAME'] = $member['name']; $_SESSION['SESS_EMAIL'] = $member['email']; session_write_close(); header("location: ../users/sadmin/index.php"); exit(); } if($table == 'administrators') { session_regenerate_id(); $_SESSION['SESS_ID'] = $member['id']; $_SESSION['SESS_SCHOOL_ID'] = $member['school_id']; $_SESSION['SESS_NAME'] = $member['name']; $_SESSION['SESS_EMAIL'] = $member['email']; session_write_close(); header("location: ../users/admin/index.php"); exit(); } if($table == 'teachers') { session_regenerate_id(); $_SESSION['SESS_ID'] = $member['id']; $_SESSION['SESS_SCHOOL_ID'] = $member['school_id']; $_SESSION['SESS_NAME'] = $member['name']; $_SESSION['SESS_EMAIL'] = $member['email']; session_write_close(); header("location: ../users/prof/index.php"); exit(); } if($table == 'students') { session_regenerate_id(); $_SESSION['SESS_ID'] = $member['id']; $_SESSION['SESS_SCHOOL_ID'] = $member['school_id']; $_SESSION['SESS_CLASS_ID'] = $member['class_id']; $_SESSION['SESS_NAME'] = $member['name']; $_SESSION['SESS_REGISTRATION'] = $member['registration']; $_SESSION['SESS_EMAIL'] = $member['email']; session_write_close(); header("location: ../users/aluno/index.php"); exit(); } } else { $errmsg_arr[] = 'Suas informacoes de login estao incorreta. Por favor, tente novamente.'; $errflag = true; $_SESSION['ERRMSG_ARR'] = $errmsg_arr; session_write_close(); header("location: ../index.php"); exit(); } } else { die("Query failed"); } ?> I have 2 tables I have settup called Users_Messages and Users_Message_Replies inside each of these tables I have a row called DateSent, I'm trying to select from both of these tables and only display the latest Sent item by ID and order them all by date. I can get it to display the items correctly using the below code, but I can't get them to order correctly by the latest date in both of the Tables. Code: [Select] $page_query = mysql_query(" SELECT MessageID, DateSent FROM Users_Messages WHERE ToID = '$user_ID' UNION SELECT MessageID, DateSent FROM Users_Messages WHERE FromID = '$user_ID' ORDER BY DateSent DESC "); while ($replycheck = mysql_fetch_assoc($page_query)){ $message_idmainnew = $replycheck['MessageID']; $date = $replycheck['DateSent']; $sql2 = "SELECT MainMessageID FROM Users_Message_Replies WHERE MainMessageID = '$message_idmainnew '"; $sql_result2 = mysql_query($sql2); $replycheck2 = mysql_fetch_assoc($sql_result2); $newreplyID = $replycheck2['MainMessageID']; $sql4 = "SELECT MessageID FROM Users_Messages WHERE MessageID= '$message_idmainnew'"; $sql_result4 = mysql_query($sql4); $messagecheck2 = mysql_fetch_assoc($sql_result4); $newmessageID = $messagecheck2['MessageID']; if ($newreplyID == NULL){ $sql2 = "SELECT * FROM Users_Messages WHERE MessageID= '$newmessageID' ORDER BY DateSent ASC"; $sql_result2 = mysql_query($sql2); $message_row = mysql_fetch_assoc($sql_result2); $message_01 = $reply_row['Message']; $date = $reply_row['DateSent']; $date1 = strtotime($date); $datemain = date('F j, Y, g:i a', $date1); }else{ $sql2 = "SELECT * FROM Users_Message_Replies WHERE MessageID= ' $newreplyID ' ORDER BY DateSent DESC"; $sql_result2 = mysql_query($sql2); $reply_row = mysql_fetch_assoc($sql_result2); $message_01 = $reply_row['Message']; $date = $reply_row['DateSent']; $date1 = strtotime($date); $datemain = date('F j, Y, g:i a', $date1); } { Is there an easier way to do this? And how would I get the dates to line up, with a Join? Thanks. This topic has been moved to MySQL Help. http://www.phpfreaks.com/forums/index.php?topic=348013.0 |
|||||||