updating more than one field

[QuizToon]

HI all

 

Title gives an idea of what I want to happen.

 

I have a database which is basically a league table with the highest point scorer at the top and the lowest at the bottom. I have been receiving each rounds scores via a spreadsheet which I then had to mess about with so that I could eventually import it to the database. It takes about an hour and a half to do for each event

 

There are several fields in the database table. e.g. id, first name, last name, event, round 1, round 2, round 3, up to round 8.

 

I know how to call up and update one record at a time but was wondering if there was a way to call up all the records for a given event, input that weeks rounds scores for each and hit the update button so they are all updated in one go.

 

If someone could tell me if it or isn't possible would be a great start and if they could point me in the direction of the right term to search on or show me a tutorial to achieve this I would be very grateful.

 

Many thanks for reading

[.Stealth]

Take a look at this:

http://www.w3schools.com/php/php_mysql_insert.asp

[QuizToon]

Thanks for the quick reply, but I have already read that and it doesnt really answer my question.  I am beginning to feel that what I want to do cant be done.

[.Stealth]

I'm not quite sure what you are wanting to do.

 

Are you editing records already in the table or just adding new records?

 

If you are adding new ones, the link above explains how, if you are updating (editing), check this one out:

http://www.w3schools.com/php/php_mysql_update.asp

[QuizToon]

HI again

 

I am updating already existing records.  Assume that it is week 3 and I need to insert/update week 3 scores into the week3 field in the database for each of the competitors.

 

At the moment I have to call each  competitor record update the field then get the next one and so on, which takes quite a long time as you have to load each competitor every time.

 

What I want is to call all the competitors into a form and have them listed one under the other, update/insert the week 3 scores for each one right there on the page then click the submit button and each week3 field for each of the competitors is updated to reflect their score for that week.

 

Hope that makes a bit more sense

 

Thanks again

[.Stealth]

Can you post up your current code you are using to do this? I will probably be able to understand better what you are doing through PHP rather than words.

 

I'm struggling to understand what your problem actually is. If you can already update 1 field you can do it for them all, all in one go using something like a foreach(). I'll help with that once I see what your code is up.

[QuizToon]

I don't currently have any code because I don't update using php at the moment, I have been using the csv file and  manually amending it before importing to the database via myphp. 

 

I just know how to select a single record from a list of records retrieved from the database, and update a given field for that record.

 

Basically I would have a script which would return the following from the database

 

player 1  -  Click to edit

player 2  -  Click to edit

player 3  -  Click to edit

player 4  -  Click to edit

player 5  -  Click to edit

player 6  -  Click to edit

player 7  -  Click to edit

 

I would then select which I wanted to edit and would then be given that players record with all the fields available to update,  it would update when the submit button was clicked

 

eg something like

 

Name            R1 Score  R2 Score  R3 Score  R4 Score  R5 Score    R6 Score  R7 Score R8 Score

John Doe          15            21            *

 

 

I would then place the next score, in this case it would be round 3, and update.

 

What I would like is to select all of the player records .......

 

eg

 

Name            R1 Score  R2 Score  R3 Score  R4 Score  R5 Score    R6 Score  R7 Score R8 Score

John Doe0          15            21            *

John Doe1          15            21            *

John Doe2          15            21            *

John Doe3          15            21            *

John Doe4          15            21            *

John Doe5          15            21            *

John Doe6          15            21            *

 

SUBMIT

 

I would then input the score for each player, eg replace the asterisk with the score and then click the submit button and all the record would update with the relevant score.

 

Obviously the asterisk wouldnt actually be there that is just to try show you what I mean.

 

Hope this makes a little better sense.

 

Thanks for trying and being patient with me, it is appreciated

 

ps i should mention that there are probably 100 players in total

 

 

[.Stealth]

This is how you would get all of the info into a form:

 

<form>
<?php
    
$mysqlConnect = mysql_connect($host, $user, $pass);

if($mysqlConnect){
	$selectDb = mysql_select_db($database);
	if($selectDb){
		$query =	"SELECT name, round1 FROM table_name;";			
		$doQuery =	mysql_query($query);

		while($row = mysql_fetch_array($doQuery)){
			$name = $row[0];
			$roundData = $row[1];
			echo "<label for=\"round\">$name</label>";
			echo "<input type=\"text\" name=\"round\" value=\"$roundData\" /><br />";		
		}
	}else{
		echo "select db";
	}
}else{
	echo "dint work";
}

?>
<input type="submit" value="submit" />
</form>

 

If you wanted to do it like i have done there, to get the weeks round field you would just edit this bit:

		$query =	"SELECT name, round1 FROM users;";	

 

Just change the "round1" to the round you need, eg "round2". Depending on the names of the fields for your rounds.

 

There might be an error or two in there, I haven't tried it but thats what you'd do. Obviously you'd have to modify it a tad to make the input field's names be correct for each round but that isn't too hard.

 

For the updating, I'll try and get something up as soon as i can for that. I need to get some more work done.

 

Just a quick question, do you know any PHP? Just so i know how much i need to explain to you.

[.Stealth]

I had a thought before. Since you don't seem to be using this for anything online and just as a database, you could use something like SQLyog.

 

Take a look:

http://www.webyog.com/en/

 

It isn't free but it's well worth the money. Saved me a lot of time on projects. Although I'm sure there is free ones out there just like SQLyog.

 

If that wont do what you want, let me know and I'll carry on with the example.

[QuizToon]

First off let me thank you for your patience and for sticking with me on this.

 

I have a little knowledge of php and I do understand it a little, I tend to get confused with it though, you know how it is, just cant get it to sink in all the time.  My problem is I find it difficult to learn from books etc, I need to be taught in a lesson like at school (long time ago that was).  Need someone to tell me and show me as well as having the book to reference to.

 

I don't think SQLyog is quite what I am looking for in this instance but looks like something I could use for other projects.

 

Going to crack on and try the code you have written up there.

 

Many thanks

 

Look forward to part 2 of my lesson

[.Stealth]

Yeah I'm the same as you regarding the learning but i really wanted to learn PHP so i bought a book and got stuck in, got help off forums when i needed it and now i'm not too bad with it. I'm no expert still but if you want to learn and your patient and do your research your scripts come together.

 

I had a go at OOP PHP but that's one thing i can't learn no matter what, I think that's something I'd need to learn in a class.

 

I'll try and make some spare time later to get the second part up.