[RESOLVED] Help with php and Sql query please

[Larry53]

I am very much new to php and MySql, but everything I do know is self learned.

I have a form that once filled in and submitted enters the correct information into my database. Now I want to retrieve that information, actually I only need to retrieve and echo sorryID into a table. I can successfully echo the sorryID into the table on my page, however I only want to data from the sorryID to to echoed if there is a corresponding squareID.

My database table is:
Table raffle

squareID name email sorryID time
5 Tom Thumb [e-mail] Sorry, I'm already taken 2010-05-03 12:48:52
3 Bertha Bugle [e-mail] Sorry, I'm already taken 2010-05-03 12:53:22
7 John Smith [e-mail] Sorry, I'm already taken 2010-05-03 12:53:42

I have a table on the raffle.php page with 10 columns and 10 rows, I am trying to figure out how to echo the sorryID column from the database into the correct corresponding table cell.

So if someone fills out the form and selects square 5, or 3, or 7, or what ever square they choose, it gets entered into the database, now I want to display only the data from sorryID into the corresponding squareID which corresponds to the correct table cell.

Someone selects table cell 5, fills in the form, submits it, then the sorryID is automatically echoed into the table cell 5.

          <?php
		  	// Make a MySQL Connection
			$db = mysql_connect("localhost", "root", "admin") or die(mysql_error());
			mysql_select_db("raffle",$db) or die(mysql_error());

		// Retrieve all the data from the "raffle" table
		$result = mysql_query("SELECT * FROM raffle");
		//or die(mysql_error());

		// store the record of the "raffle" table into $row
		$row = mysql_fetch_array($result);
		// Print out the contents of the entry

		//echo "Name: ".$row['sorryID'];
	  ?>

	  <td width="92" height="50" align="left" valign="top" class="number">1<br />
              <p align="center"><?php echo  $row["sorryID"]; ?></p>
              </td>

Right now the sorryID data is echoed to every table cell, but that is not what I want. Is this possible, and if it is, could one of you knowledgeable people please help me. This is for a fundraiser for a non-profit dog rescue group that is hurting for donations.

Here is a link to the page I am using
http://www.pap911rescue.com/raffle/spring_raffle.php

Thank you for any and all help.

Larry

MOD EDIT: [noparse]

..

[/noparse] bbcode tags added.

[Tonsil]

I reworked this a little bit for you. I load up all of the records first, then look for existing values while I'm looping to generate the table. Keep in mind that mysql_fetch_array only retrieves a single record; you need to get all of the records if you want to have them available.

<?php

// Make a MySQL Connection 
$db = mysql_connect("localhost", "root", "admin") or die(mysql_error()); 
mysql_select_db("raffle",$db) or die(mysql_error());

// Retrieve all the data from the "raffle" table 
$query = mysql_query("SELECT * FROM raffle");
$results = array();

// store the record of the "raffle" table into $row 
// index the stored results by squareID for easier lookup later
while ($row = mysql_fetch_assoc($query)) $results[$row['squareID']] = $row;

$table_width = 10;
$table_height = 10;
$current_cell = 0;
echo "<table border='1'>\n";
for($row=1; $row<=$table_height; $row++) {
    echo "<tr>\n";
    for($column=1; $column<=$table_width; $column++){
        $current_cell++;
        echo "<td>";
        echo $current_cell;
        if(isset($results[$current_cell]) && isset($results[$current_cell]['sorryID'])) {
            echo "<br />" . htmlentities($results[$current_cell]['sorryID']) . "<br />\n";
        }
        echo "</td>\n";
    }
    echo "</tr>\n";
}
echo "</table>\n";
?>

I hope this helps - feel free to ask if you need any further explanation.

[Larry53]

Wow, thank you Tonsil for your prompt and very accurate reply. Your reply work almost perfectly, well it works but once someone fills out the form and submits it, the text "Sorry, I'm taken" does show in the correct table cell, it just stretches the table cell way out. However once every table cell in the same row is filled in everything looks right.

I was able to assign a class to the table so the table is the correct width and height, and has the background gradient that we wanted, so I got that implemented correctly.

The only question I have is, is there a way to assign a width and height to the table cells themselves so they are a certain width and height?

Here is a link to the page, http://www.pap911rescue.com/raffle/spring_raffle.php, if you fill in the form and click submit, you will see what I am talking about with the table cells once they are filled in.

I did try to insert a line break into the form hoping that it would not stretch the table cells but that didn't work as planned. In the form in a hidden field I inserted, '/n' thinking that would work, but again it didn't.

Other than that, you are a lifesaver, and I certainly did learn from your excellent, and perfectly working code. I thank you for your help.

Larry

[Tonsil]

Glad to help, Larry. I looked at your link and it looked fine to me, so you may have gotten it sorted out. To answer your question, you can definitely style the individual cells. For example, you could have in your stylesheet something like the following:

.raffle_table td {
    width: 10%;
}

You could do a percentage, or a specified width like 40px.

On another note, I was going to suggest a possible improvement. In your database, the sorryID column seems like it's storing the same string repeatedly. If that's true, you might not need to store that in the database. It seems like once somebody claims a row, then a record is created in the database, thus signifying that the square is taken. You could store that string in the code instead. When you generate the table, you can just output that string any time you have a square that's taken. That way, you only have to store the string in one place, and if you decide to change it, you can do so easily. Here's an example of what I'm talking about:

// ... (this is an excerpt)

$table_width = 10;
$table_height = 10;
$current_cell = 0;
$sorry_string = htmlentities("Sorry, I'm taken");
echo "<table border='1'>\n";
for($row=1; $row<=$table_height; $row++) {
    echo "<tr>\n";
    for($column=1; $column<=$table_width; $column++){
        $current_cell++;
        echo "<td>";
        echo $current_cell;
        if(isset($results[$current_cell])) {
            echo "<br />" . $sorry_string . "<br />\n";
        }
        echo "</td>\n";
    }
    echo "</tr>\n";
}
echo "</table>\n";

[Larry53]

Once again Tonsil, you have hit the nail on the head, perfect fix. The only thing I can say in my defense is that I have been working on this for so long, that my head is numb. I should have been able to figure that fix for the table cells out myself as I attached the table class, I have no idea why I didn't think of the table.td fix.

I also never even gave it a thought about your suggestion for the improvement in the database, that is another excellent fix.

I can do nothing but thank you for your help, and help me you certainly did. Thank you, and I hope that someday I will be able to give back to the web community.

Have a good day.

Larry

Tonsil;10952031 wrote:

Glad to help, Larry. I looked at your link and it looked fine to me, so you may have gotten it sorted out. To answer your question, you can definitely style the individual cells. For example, you could have in your stylesheet something like the following:

.raffle_table td {
    width: 10%;
}

You could do a percentage, or a specified width like 40px.

On another note, I was going to suggest a possible improvement. In your database, the sorryID column seems like it's storing the same string repeatedly. If that's true, you might not need to store that in the database. It seems like once somebody claims a row, then a record is created in the database, thus signifying that the square is taken. You could store that string in the code instead. When you generate the table, you can just output that string any time you have a square that's taken. That way, you only have to store the string in one place, and if you decide to change it, you can do so easily. Here's an example of what I'm talking about:

// ... (this is an excerpt)

$table_width = 10;
$table_height = 10;
$current_cell = 0;
$sorry_string = htmlentities("Sorry, I'm taken");
echo "<table border='1'>\n";
for($row=1; $row<=$table_height; $row++) {
    echo "<tr>\n";
    for($column=1; $column<=$table_width; $column++){
        $current_cell++;
        echo "<td>";
        echo $current_cell;
        if(isset($results[$current_cell])) {
            echo "<br />" . $sorry_string . "<br />\n";
        }
        echo "</td>\n";
    }
    echo "</tr>\n";
}
echo "</table>\n";