Help echoing mysql table data

General PHP Help
#1

Hi i currently have 5 tables in mysql database. Each table has a set of questions so therefore each table is a section. The programme that I am running allows the table to randomly select 1 question from each section. However I only need 4 questions and I have 5 sections. I want one section to be excluded each time. I would like my code to randomly select 4 sections and take a question from each and echo it out. I would also like it to stop echoing the same question twice. Here is my code:

<?php //Germany Section1 $query="SELECT * FROM Germany order by RAND() LIMIT 1;"; $fetch = mysql_query($query) or die ("Could not find Question"); while ($row = mysql_fetch_assoc($fetch)){ echo $row['Question']."
"; } //Britain Section2 $query="SELECT * FROM Britain order by RAND() LIMIT 1;"; $fetch = mysql_query($query) or die ("Could not find Question"); while ($row = mysql_fetch_assoc($fetch)){ echo $row['Question']."
"; } //Russia Section3 $query="SELECT * FROM Russia order by RAND() LIMIT 1;"; $fetch = mysql_query($query) or die ("Could not find Question"); while ($row = mysql_fetch_assoc($fetch)){ echo $row['Question']."
"; } //France Section 4 $query="SELECT * FROM France order by RAND() LIMIT 1;"; $fetch = mysql_query($query) or die ("Could not find Question"); while ($row = mysql_fetch_assoc($fetch)){ echo $row['Question']."
"; } //Italy Section 5 $query="SELECT * FROM Italy order by RAND() LIMIT 1;"; $fetch = mysql_query($query) or die ("Could not find Question"); while ($row = mysql_fetch_assoc($fetch)){ echo $row['Question']."
"; } ?>
#2

Just off the top of my head I would just union all the results together and select a random 4 from the 5 results…

What I don’t understand is how it is echoing the same question twice, unless you have the same question in the different tables?

#3

I can see what you’re saying but see the problem is the 4 questions generated need to be about the 4 out of the 5 countries. Is there a way to make it stop choosing questions with similar words if i do put them together for example?
Thanks for your help

#4

You have a bad database design and are using obsolete MySQL calls.

#5

Okay but could actually help me? Im new to php learning off youtube videos. Will somebody answer my question - how can i stop the code echoing similar questions if i do put the data together. For example how can i stop it echoing questions with France in the question twice? and HOW?

#6

I am still trying to figure out what you’re trying to do.

“how can i stop the code echoing similar questions if i do put the data together. For example how can i stop it echoing questions with France in the question twice? and HOW?”

Sorry and no offense, but that makes no sense.

As already stated you’re using obsolete code, why if you’re new to PHP learn something that 99.1 percent of people coding in PHP are moving away from.

[php]$query=“SELECT * FROM Russia order by RAND() LIMIT 1”;[/php]
First of all you had an extra ; in the query statement and second you shouldn’t be using * but instead pulling specific columns from the database table.

Here’s an example what I’m talking about :
[php]$query = “SELECT id, confirm, question, answerA, answerB, answerC, answerD, correct FROM movieTrivia WHERE id=:current_id AND confirm != ‘no’ ORDER BY id ASC LIMIT 1”;[/php]
This will also work with mysqli or PDO with a few modifications.

This will help in run time for the query doesn’t have pull every thing from the db table.

My suggestion is to throw away that video that you got from the internet, try to rephrase you’re question.

My something like I have five countries, when a player (user) picks a category it will spit out one of four possible questions? However, like I said I have no idea what you problem is to help you out and judging from the other responses posted I don’t think I am alone.

#7

Let me be specific then:
I have 5 sections called different countries - France, Britain, Germany, Russia, Italy.
Each country/section has a set of questions.

I want my code to randomly select 4 countries/sections out of the 5 and pick a random question from each.
I can’t break it down any further than this.
Help me

#8

This is what I asked you to do, just union the statements together and randomly grab 4 of the 5 results.

[php]<?php
$query=“SELECT Question FROM (SELECT Question FROM Germany order by RAND() LIMIT 1 UNION SELECT Question FROM Britain order by RAND() LIMIT 1 UNION SELECT Question FROM Italy order by RAND() LIMIT 1 SELECT Question FROM Russia order by RAND() LIMIT 1 SELECT Question FROM France order by RAND() LIMIT 1) A ORDER BY RAND() LIMIT 4;”;
$fetch = mysql_query($query) or die (“Could not find Question”);
while ($row = mysql_fetch_assoc($fetch)){
echo $row[‘Question’]."
";
}
?>[/php]

#9

Thank you top coder! You’ve been the most help so far.
My only question is if I do union the questions together, how do I stop my code from echoing more than 1 question from each country. I need it to fetch one question from each of the 4 out of 5 countries

#10

What I posted does exactly what you asked, did you test it yet?

#11

Oh i see! Yeah I’m getting a ‘could not find question’ error.
My table names look correct : Britain, France, Germany, Italy, Russia.
This is currently the code + what you gave me.

$conn = mysql_connect ($db_host,$db_username,$db_pass) or die ($dberror1);
$select_db = mysql_select_db (‘cl21-aaronexam’)or die (“no database”);
?>

Questions

Europe

<?php $query="SELECT Question FROM (SELECT Question FROM Germany order by RAND() LIMIT 1 UNION SELECT Question FROM Britain order by RAND() LIMIT 1 UNION SELECT Question FROM Italy order by RAND() LIMIT 1 SELECT Question FROM Russia order by RAND() LIMIT 1 SELECT Question FROM France order by RAND() LIMIT 1) A ORDER BY RAND() LIMIT 4;"; $fetch = mysql_query($query) or die ("Could not find Question"); while ($row = mysql_fetch_assoc($fetch)){ echo $row['Question']."
"; } ?>

Should there be more Unions in the brackets you created? could that be the problem?

#12

I had 2 syntax errors, below should work.

[php]$conn = mysql_connect ($db_host,$db_username,$db_pass) or die ($dberror1);
$select_db = mysql_select_db (‘cl21-aaronexam’)or die (“no database”);
?>

Questions

Europe

<?php $query="SELECT Question FROM (SELECT Question FROM Germany order by RAND() LIMIT 1 UNION SELECT Question FROM Britain order by RAND() LIMIT 1 UNION SELECT Question FROM Italy order by RAND() LIMIT 1 UNION SELECT Question FROM Russia order by RAND() LIMIT 1 UNION SELECT Question FROM France order by RAND() LIMIT 1) A ORDER BY RAND() LIMIT 4;"; $fetch = mysql_query($query) or die ("Could not find Question"); while ($row = mysql_fetch_assoc($fetch)){ echo $row['Question']."
"; } ?> [/php]
#13

Your putting a band-aid on a broken arm. Stop what your doing. Study database normalization and PDO. Once you understand those two things, then try to build something.

#14

Still saying could not find question: Anything else?

$conn = mysql_connect ($db_host,$db_username,$db_pass) or die ($dberror1);
$select_db = mysql_select_db (‘cl21-aaronexam’)or die (“no database”);
?>

Questions

Europe

<?php $query="SELECT Question FROM (SELECT Question FROM Germany order by RAND() LIMIT 1 UNION SELECT Question FROM Britain order by RAND() LIMIT 1 UNION SELECT Question FROM Italy order by RAND() LIMIT 1 UNION SELECT Question FROM Russia order by RAND() LIMIT 1 UNION SELECT Question FROM France order by RAND() LIMIT 1) A ORDER BY RAND() LIMIT 4;"; $fetch = mysql_query($query) or die ("Could not find Question"); while ($row = mysql_fetch_assoc($fetch)){ echo $row['Question']."
"; } ?>
#15

This is becoming fun…

$query=“SELECT Question FROM ((SELECT Question FROM Germany order by RAND() LIMIT 1) A UNION (SELECT Question FROM Britain order by RAND() LIMIT 1) B UNION (SELECT Question FROM Italy order by RAND() LIMIT 1) C UNION (SELECT Question FROM Russia order by RAND() LIMIT 1) D UNION (SELECT Question FROM France order by RAND() LIMIT 1) E) F ORDER BY RAND() LIMIT 4;”;

Even if the above works, Kevin is right, you should really have 2 tables one for the language ID and the other with all the questions that links to the languages.

But sometimes in the real world, you aren’t afforded such luxuries to change database designs and have to improvise the best that you can.

I’m guessing this is for a school assignment and your teacher most likely wanted you to come up with a solution either through code or a query to present the information he’s looking for. I’m not going to judge whats right or wrong, because I don’t know all the details of what you are doing and constraints you are working with.

#16

Thanks Topcoder. I think I will set up the tables the way you recommend.
How would it be coded though? Could you give me an example?

#17

Set the tables up first and show me the 2 tables and columns and then we can write the query.

You also might want to ask [member=46186]Kevin Rubio[/member] to help write the PDO statements, but after you get the tables and query set correctly.

#18

Well, everyone, I would like to jump in and throw my two cents into the mix.

First, Kevin is 100% correct that the way the questions are pulled will cause issues, especially if you have
hundreds of people looking for questions… How do you keep track of who out of the hundreds of users
have seen which questions? It might be best to create a database of a list of groups of questions instead.
Then, each user would have a question level number saved in there user info. They would just get the next
level of questions and the level number would be incremented in their user profile. The questions would be
randomized ahead of time into groups and use the group number to pull the questions out of the list.

Next, Topcoder did give you the way to randomly pull out the questions needed from your current layout.
It should work correctly. I prefer recreating the database to fit the uses better than using a large amount
of joins. Although, in some cases it is best to use simple database layouts and use tricks such as I did say
about creating a second table of groups of questions…

Lastly, Kevin and others here prefer PDO. In most of my work, I use MySQLi… Seems easier since I do not
use a lot of classes. Anyone who needs to use or prefers to use classes should learn PDO. Also, it is really
a question of the questions… LOL If you delete questions a lot or add many often, then you might need
to recreate the groups of questions and start all the user’s over at the first group. I think your project does
need to be thought out a bit further from the database end.

Good luck!

#19

Thanks for your advice. The questions don’t need to be answered. All I want is for the code to generate questions that aren’t similar to others or from the same table/section(countries). It’s for an examination style website that students can use to study however they won’t be answering the questions on the website they will only generate the questions and print them off! I’m currently learning web development and am still an amateur but I’m doing a udemy course to get me where I want to be.

#20

Well, that answers a bunch of questions. Still, it does not answer how the questions are being handled.
If you don’t answer questions, we can’t help you if we don’t understand your needs. Topcoder showed
you how to do it. But, one set of four questions is not much of a test. And, if you randomly pick four out
of a thousand, you will get dupes after a short period, longest being 1/25 or so displays. Four out of
a thousand is 250, two passes, eight out 1000 is 125, etc… Just a few displays and it would hit dups.

Anyway…

The first part is how often are the questions added or changed? If you just want them to randomly show
then Topcoder’s Join query should work. As far as keeping track of them, that is hard without flags or other
systems to remember which were already seen. Just randomly grabbing the questions works. But, then,
you have to have some way to know which ones you just showed. How can you do that? Well, the two
simplest ways would be to keep track of each user and which question they have already seen which would
involve a huge list for each user or group all the question into fours and keep just the current group number
for each user which would involve keeping track of one single number per user. The second is much much
easier. It does require creating the groups which would take just a few lines of code and only run one time.

Now, back to the questions again… Do you plan to add and delete questions often? Are you planning to
alter the list often? These are really simple or basic questions, but, it will make a big difference on how you
use the questions. If they are created once and used for a year, then no problem. If they are changed
every month, other issues. Design is best if thought out ahead.

So, in my humble opinion, when questions are created and that process is complete, you can run the group
code which could create a new or maybe newer list of groups of four questions. Then, everyone’s user
profile would be set to group number one. When the log in they get the first four questions and their
group number would be incremented to number two, etc…

That is just one way to do it with ease. Little code. Also, if in your examples where you use different
countries, this is for different country questions, the user can be set to just one of these, so less code
needed for that part as you just use the group list for their country.

Still not completely clear on your overall project, but, hope that these comments made some sort help
for you…

Good luck!

Sponsor our Newsletter | Privacy Policy | Terms of Service