need to load an image based on a database table status

[cyr0n_k0r]

I have a grid of 50 lots.

Each lot is a separate image. They are all unsold so each one is white. However, when someone buys a lot another database guy is going to make a form that will change the status of a table in the database to show that the lot is now either reserved or sold.

If the lot is reserved, the lots image needs to change from white to gray. If the lot is sold, the image needs to turn to red.

How can I accomplish something like this. I can dabble with PHP a little, but nowhere near this scale.

 

[mikeblas]

You'll need to select the status data for each of the 50 lots from the database. Then, in your PHP script, build a visual representation of the 50 lots based on the status you've got from the database.

It shouldn't be more than a couple of dozen lines of code.

If you can provide any specifics, then we can provide more detailed advice.

 

[cyr0n_k0r]

I assume its going to be either an access or mysql database type.

I don't know much about how databases function but basically each lot would be a seperate entry in the database and one of the subsets of each lot would be its status. The status is either, open, reserverd, or sold.

Then on the website a script checks the status of EACH LOT and depending on that specifics lots status, it displays a different image for each lot.

 

[mikeblas]

It won't be a subset; I'd expect it to be an attribute.

Again, once you have any useful details and take a crack at writing the code, fill us all in and we'll be able to help you get it working.

 

[MadJuggla9]

You can setup a very simple database. Would be very easy if you have any skill with PHP and MySQL

check next thread

 

[MadJuggla9]

Like so:

http://chrisg.serveftp.com/scripts/my%20scripts/php_table_db/


Steps:
1) Create DB in mysql using mysql prompt:

mysql> CREATE DATABASE grid_50;
Query OK, 1 row affected (0.00 sec)


mysql> use grid_50;
Database changed


mysql> CREATE TABLE properties (
    -> prop_num int,
    -> status varchar(10) NOT NULL default 'available',
    -> info varchar(255) NOT NULL default ''
    -> );
Query OK, 0 rows affected (0.02 sec)

2) Make the entries (fill_your_tables.php):

<?php

mysql_connect("localhost", "username", "password") or die("Cant connect to DB");
mysql_select_db("grid_50") or die("Cannot select database");

for($i=1; $i<51; $i++) {
	$query = "INSERT INTO properties " . 
			"(prop_num, status, info) VALUES " . 
			"($i, 'available', '')";
	mysql_query($query) or die("Cant insert rows");
}
echo "Successful";

?>

3) View your grid (grid.php):

<table border=1>

<?php
$prop_num	= 1;
$height		= 60;
$width		= 125;
$cols		= 5;
$rows		= 10;

for($x=0; $x<$rows; $x++) {

	echo "<tr>";
	for($y=0; $y<$cols; $y++) {
		echo "<td height=$height width=$width>";
		
			//------------------------------------------------------------------------
			echo "<a href=edit.php?prop_num=$prop_num>property $prop_num</a>: ($x, $y)<br>";
			echo "$content_for_cell";
			//------------------------------------------------------------------------
			
		echo "</td>";
		$prop_num++;
	}
	echo "</tr>";
}


?>

</table>

4) Make your editable content ... and build onto it from here (edit.php)

<?php
if(isset($_GET["prop_num"])) {
	$prop_num = $_GET["prop_num"];
	echo "You wanted to edit property $prop_num";
} else {
	echo "No property to edit";
}
?>

 

[MadJuggla9]

It just occured to me, how many here do real estate pages or area maps in webpages via php? I'll be doing PHP/MySQL for a place that wants to keep track of properties over a large amount of land for commercial use.

How do you guys go about it?

 

[mikeblas]

Where's the code that gets the state of the cell from the database?

 

[MadJuggla9]

<?php
if(isset($_GET["prop_num"])) {
$prop_num = $_GET["prop_num"];
echo "You wanted to edit property $prop_num";
} else {
echo "No property to edit";
}
?>

match the prop_num to the cell .... whats hard?

 

[mikeblas]

I still don't see anything that connects to the database. The code that inserts into the database has been posted, but I see nothing that updates the database and nothing that reads from it.

 

[MadJuggla9]

I still don't see anything that connects to the database. The code that inserts into the database has been posted, but I see nothing that updates the database and nothing that reads from it.

<?php

mysql_connect("localhost", "username", "password") or die("Cant connect to DB");
mysql_select_db("grid_50") or die("Cannot select database");

for($i=1; $i<51; $i++) {
$query = "INSERT INTO properties " .
"(prop_num, status, info) VALUES " .
"($i, 'available', '')";
mysql_query($query) or die("Cant insert rows");
}
echo "Successful";

?>


That connects to the database ....... Other than that, editing is up to the end user. I provided a template, I'm sure as hell not going to write the whole thing for him ..... that would be like doing someone's homework.

 

[mikeblas]

That connects to the database

Right. Like I said, the code that inserts to the database has been posted, but nothing updates the database and nothing reads from it. So the OP's question remains unanswered; it's not up to him to insert things into the database, as the database is maintained by someone else.

 

[MadJuggla9]

Right. Like I said, the code that inserts to the database has been posted, but nothing updates the database and nothing reads from it. So the OP's question remains unanswered; it's not up to him to insert things into the database, as the database is maintained by someone else.

I didn't see that he just maintains .... he asked for general DB/PHP help. I don't see where this is a collaboration. Surely that's enough to get him started. Other than that it's called google.

OP, google: "mysql" "update"
and: "mysql" "select"

To edit it will be something like this in your edit page assuming the property was found

<?php
mysql_connect("localhost", "username", "password") or die("Cant connect to DB");
mysql_select_db("grid_50") or die("Cannot select database");

$query = "UPDATE properties " . 
     "SET status='sold' " . 
     "WHERE prop_num='$prop_num'";
mysql_query($query);
?>
</php

and to query it, something like so:

<?php
mysql_connect("localhost", "username", "password") or die("Cant connect to DB");
mysql_select_db("grid_50") or die("Cannot select database");

$query = "SELECT * " . 
     "FROM properties " . 
     "WHERE prop_num = '$prop_num'";
$row = mysql_query($query);

while($row = mysql_fetch_array($row)) {
     echo $row["prop_num"];
     echo $row["status"];
     echo $row["info"];
}
?>

did not care to test it all, probably has some typos

 

[mikeblas]

Your proposed solution is going to do 50 distinct select statements, each completely different, when drawing the grid. Do you think that's going to provide adequate performance?

 

[MadJuggla9]

This is something to build onto, please remember that. The table would query first, then populate the corresponding grid as it goes through the loop. 50 grids .... perhaps a subtle slow time, but pathetically small in size and very simply for such a simplistic application.

Perhaps you'd rather rewrite a more efficient method and give him every file for the exact solution he wants with absolutely no work or learning.

I'm just trying to help here. I think he has lost interest anyways

 

[mikeblas]

Perhaps you'd rather rewrite a more efficient method and give him every file for the exact solution he wants with absolutely no work or learning.

Nope; I'd rather give him a strong shove in the right direction (as I've already done), or help him fix code that he came up with. As I pointed out earlier, it's impossible to give an exact answer because he's posted no information about his database schema.

Running 50 singleton selects is going to be at least 50 times slower than a single select which generates a result set with the needed data.

 

[cyr0n_k0r]

Thank you everyone for your help. The job of coding this is no longer mine. I appreciate the help though and will point the new programmer to this thread.