tricky mysql & php problem

[James Smith]

Hi all.

How can i go about splitting results into individual vars, as supposed to putting them into an array for use in a while loop ?

This is my problem. http://www.hiphopmusic.co.uk/armeggedon/table.html

I have a mysql table for the entries like this :

ID, name1, name2, round

theres more, but lets just leave it at that). This, for example, would be used as name1 vs name2. 'Round' says whether it's first, quarters, semis etc

Now, how can i get the results and use them as links in that table. What I mean by this is do a query, then all of the entries in that html table where it says "1 vs 2" are filled in from the DB.

I'm not sure if I've made myself totally clear, but please help if you can. It will save me a lot of time !

Cheers
James

[PeterW]

James, your "notfound.php" seems to send me into a constant loop. I saw you come into the chat room earlier, sorry I was `homeworking`
I am quite sure I know what you mean.. perhaps you could explain more?

[James Smith]

http://www.hiphopmusic.co.uk/armageddon/table.html

If you look there maybe it will be a bit clearer.

ta
j

[freddydoesphp]

I am not sure of what you would like the outcome to be either, could you explain a little better?

[James Smith]

Sorry,

Basically what I want to do is to be able to put results from the DB into that table.

Which is obviously hard because it isn't in a straight list format as you might usually find doing straight result queries from mySQL then putting into say $row[], and echoing that in a while loop.

What I want to be able to do is echo the first result where i want, then the second result, then the third etc. Obviously I could do this with 30 seperate queries saying LIMIT 1, but that would be slow. Is there any other way of doing this.

J

[freddydoesphp]

So let me see if I got this right, is the 1 vs 2 stored in the name1 field? Could you show a sample record with the data. I think the best way is to use mysql_fetch_array() to loop through t the results and put them into a new multi-dimensional array that could be used out side the while loop. In order for me to determine the best way to structure this array I would need to see a sample record wit hits data. But just to give you a sample

$result = mysql_query("somequery");
while ($row = mysql_fetch_array($result)) {
$id = $row["ID"];
$newarray[$id] = array("name1" => $row["name1"],
"name2" => $row["name2"], "round" => $row["round"]);
}

NOw you can access the elements of the array by doing

print $newarray[1]["name1"];
where the 1 in $newarray[1] is the ID from the record in the db.

Does this help?

[James Smith]

Freddy, you are pretty much a god. I think much karma needs to be awarded !!!

ID round name1 name2 name1url name2url
1 1 James Smith Paul Jones http://www.hiphopmusic.co.uk/armaged...es_Smith-1.ram http://www.hiphopmusic.co.uk/armaged...ul_Jones-1.ram


That is an example result (it may be a little skewed on the forum).

I will try this out later, thanks for your invaluble help. You couldn't by any chance take me through it and explain what each part does so I can learn it myself ?

Thanks very much
James

[freddydoesphp]

I was just wanting to see a sample record because I am still unsure how you will be able determine which record goes in what table cell of the html table. As for the way the code works let me explain
//This is the initial query
$result = mysql_query("somequery");

//Here i setup the whiloe loop to go through each record of the resultset
while ($row = mysql_fetch_array($result)) {

//Here I assign the id row to the variable $id
$id = $row["ID"];


//This is where it gets tricky, I make a new array with the key of the array being the id field from the database. Then I create another array where the keys name1, name2 and round with each key holding the value from the database.
$newarray[$id] = array("name1" => $row["name1"],
"name2" => $row["name2"], "round" => $row["round"]);
}

So now you have an array that has the key of the id row and the value of another array that holds each of the other fields from the table.

So $newarray[2] is itself another array holding the values from the record with the id 2 and each element can be accessed by calling $newarray[2]["fieldname"]

You could also loop through the array and look for certian criteria like
while(list($key,$val) = each ($newarray)) {
if ($newarray[$key]["round"] == "semi") {
print $newarray[$key]["name1"];
}

If you loop through the array remember to reset the pointer to the beginning of the array so you can loop through it again if you need to, so use

reset($newarray);
}

[James Smith]

Freddy, thanks again.

Basically, now i can just echo in the relevant table cell the relevant row.

eg

echo("$newarray[1]["name1"] vs $newarray[1]["name2"]");

Thanks again,
James

[freddydoesphp]

No problem!

[James Smith]

Still need help.

How do i work out what round the entry is in.

I changed the initial query to :

$result2 = mysql_query("SELECT * from armageddon WHERE round = '2'");

while ($row2 = mysql_fetch_array($result2)) { 
$ID2 = $row2["ID"]; 
$newarray2[$ID2] = array("name1" => $row2["name1"], 
"name2" => $row2["name2"], "round" => $row2["round"]); 
} 

$result3 = mysql_query("SELECT * from armageddon WHERE round = '3'");

while ($row3 = mysql_fetch_array($result3)) { 
$ID3 = $row3["ID"]; 
$newarray3[$ID3] = array("name1" => $row3["name1"], 
"name2" => $row3["name2"], "round" => $row3["round"]); 
} 

$result4 = mysql_query("SELECT * from armageddon WHERE round = '4'");

while ($row4 = mysql_fetch_array($result4)) { 
$ID4 = $row4["ID"]; 
$newarray4[$ID4] = array("name1" => $row4["name1"], 
"name2" => $row4["name2"], "round" => $row4["round"]); 
}

Then I tried echoing this for example :

 if(isset($newarray4[1]["name1"])) {
    echo("".$newarray4[1][name1]." vs ".$newarray4[1][name2]."");
    }
    else {
    echo("coming soon");
    }

This isn't working. I know there is a flaw, but it's too complicated for my brain to handle at the moment, so can ya help again ?

Cheers
James

[freddydoesphp]

You should only need to run one query that is the beauty of it.

You should be able to do

$number_of_rounds = 4;
for($i=1;$i<=$number_of_rounds;$i++) {
while(list($key,$val) = each($newarray)) {
if ($newarray[$key]["round"] == $i) {
if ($newarray[$key]["name1"] != "") {
echo("".$newarray[$key][name1]." vs ".$newarray[$key][name2]."");
}
else {
echo("coming soon");
}
}
}
reset($newarray);
}

[James Smith]

Using that code I get this result :

http://www.hiphopmusic.co.uk/armageddon/aa/index.php

Thanks
J

(sorry for being a cut n paste pain)

[freddydoesphp]

I am having a hard time figuring out just how you are attempting to display the data. In round 1 will there always be 4 matches? could you possibly explain how this thing is supposed to work? You will hav access to which round by calling on $newarray[1]["round"] that would be the round that the entry which has the ID number 1 is in. I still don't get how this thing works.

[James Smith]

Freddy, basically what I want to do is fill in that table using the data in the DB.

How do I fill in the info for the later rounds ?

In my DB, round 1 = '1', the quarters = '2', semi's = '3' and the final = '4'.

Thanks for your patience.

James

[freddydoesphp]

So does that mean there will be 4 records that have 1 as their round number? I am still fuzzy abou the data structure you are using but I think you should be able to use for getting a round 1 record

while(list($key,$val) = each($newarray)) {
if (($newarray[$key]["round"] == 1) && ($newarray[$key]["name1"] != "")) {
echo("".$newarray[$key][name1]." vs ".$newarray[$key][name2]."");
}
else {
echo("coming soon");
}
reset($newarray);
}

[PeterW]

So, do you just want to do something different for each round??
If so (based on Freddy's code)

while(list($key,$val) = each($newarray)) {

switch($newarray[$key]["round"])
{
case 1:
echo("".$newarray[$key][name1]." vs ".$newarray[$key][name2]."");

break;

case 2:
echo ("Quarter final.");

break;

case 3:
echo ("Semi final.");
break;

case 4:
echo ("The final.");

break;
}

}

reset($newarray);

I dont understand really what you are trying to either

[James Smith]

I'm sorry, I'm really being vague here....

what i want to be able to do is simply fill in the table with the correct entries that are in the DB.


ie the entries that where round = 3 in the 3rd round, the entries where round = 1 in the 1st round and so on..

James

[Bartimeus]

If i understand you correctely you want to separate entries that are in the 1.st round from thoose in the 2.round, the semies and the final. Took alook on your file and i think i understand what you want.

Before i (and i belive we) can give you any good answer you need to describe your datastructure to us?

Define the table you use for the teams, the "matches" and the results. Or however you defined your data.

[James Smith]

OK.

The entries are stored in rows like this :

each row has an ID

each row has a "round" number. 1 for the 1st, 2 for the quarters, 3 for the semi's and 4 for the final

each row has a column called "name1" - the name of the 1st competitor and "name2" the name of the 2nd.

Hope that helped.

Thanks
James

[freddydoesphp]

The biggest problem that I see right now is that there can be multiple records that have round 1 or round 2 and you have a display that is multi level, by that I mean there are four records to be shown for round 1 and two for round 3 and so on.. So there really isn't a way to standardize your display unless there will always be the same number of records in each round. Maybe you could show the page you are using to display this stuff.

[James Smith]

The page i will be using to display this is http://www.hiphopmusic.co.uk/armageddon/index.php

So is there no solution to this ? or do i need to re-design my DB structure.

Thanks
J

[freddydoesphp]

James can you show us the actual code not just the rendered html that the browser shows?

[James Smith]

I'm sorry, I don't quite understand what you mean. The only code I'm using to make that table is html.

Thanks
James

[freddydoesphp]

Yeah but you are using php to generate the thml right? Or at least to write the data to the screen, so just giving us the link allows to view the source after it has been rendered to the borwser therefore ommitting the important php that makes the display happen