What is the best Field Type for my Value

unrealindeed · June 13, 2011

Hi There,

My friend has asked me to update his dads website, as the old developer hasnt time, I am not new to PHP/MySQL but not a pro at writing it either.

The site is for selling cars (small 3 table db, dm_car_details, dm_car_pictures & dm_users).

At the moment there is one page that returns all cars in the DB. My friend wants me to ad a field in the db for the car status New/Used, and display the results of each on separate pages.

I have been researching this, but still not sure the best option for my new status field type.

In dm_cars_details I have created a my field cd_status and set the type to enum and the values are ('new','used'), new being the default,

Is this the best option, or would varchar do?

There is a manage.php page that his dad can login to and add/edit cars, and I have the field included in there, but I cant get it to return the value from the database to the add new/update form.

I am looking to learn from this experience, but need some help in understanding the best way to approach this from the start,

Any help is much appreciated.

I have attached some screens to help explain my scenario.

Unreal

[attachment deleted by admin]

fugix · June 13, 2011

in your case, enum field type will be fine, varchar would work also, since you already have enum type specified I would leave it at that...do you have some code that you can post showing where exactly you are having trouble.

unrealindeed · June 13, 2011

Hi Fugix,

Thank you for your fast reply.

The site is already built & there is other fields that are pulled from the DB when the user wants to update a car, so i copied one of these exactly, and created a new instance of it renamed to cd_status to reflect the new field i created in the db.

The difference is that all original fields are pulled into stanard input boxes that can be updated and save.

But my cd_status is a select dropdown option becuase the only options are New & Used. So I have to attempt to write some php/html code that creates a slect form with the values New & Used (which are being pulled from the DB).

Here would be a good place for me for start, so i can add the code to the "Add New Car" form & "Update Car" form that allows the user to select a status when adding cars, and also update car status for existing cars.

So the below code gets the info for the particuylar car we want to update:

<?php        
          } else  { 
		require 'db_connect.php';	
            $cd_id=$_GET["cd_id"]; 
            $sql="select * from dm_car_details where cd_id=$cd_id"; 

		$result=mysql_query($sql);
		echo mysql_error();				

            while($row=mysql_fetch_array($result)) { 

                $cd_id= $row['cd_id']; 
			$cd_status = $row['cd_status'];
			$cd_car_reg = $row['cd_car_reg'];
			$cd_make = $row['cd_make'];
			$cd_model = $row['cd_model'];
			$cd_engine = $row['cd_engine'];
			$cd_fuel_type = $row['cd_fuel_type'];
			$cd_body_type = $row['cd_body_type'];
			$cd_year = $row['cd_year'];
			$cd_colour = $row['cd_colour'];
			$cd_mileage = $row['cd_mileage'];
			$cd_doors = $row['cd_doors'];
			$cd_owners = $row['cd_owners'];
			$cd_price = $row['cd_price'];
			$cd_other_details = $row['cd_other_details'];
			$cd_photo_yn = $row['cd_photo_yn'];				

            } 
        ?>

And the update form is:

<form name="form1" method="post" action="formsubmit.php?mode=update"> 
            <table width="500" border="1" align="center" cellpadding="2" cellspacing="2"> 
              <tr> 
                <td><strong>Update Car</strong></td> 
                <td><input type="hidden" name="cd_id" value="<?php echo $cd_id; ?>"> 
                   </td> 
              </tr> 
			<tr> 
			<td width="100">Machine Status</td>
			<td>
                
                <select name="status" id="status">
			    <option value="new">New</option>
			    <option value="used">Used</option>
		      </select>
                </td>
			</tr>
                <tr> 
			<td width="100">Machine Reg</td>
			<td><input name="car_reg" type="text" id="car_reg" value="<?php echo $cd_car_reg; ?>"></td>
			</tr>			  
			<tr> 
			<td width="100">Make</td>
			<td><input name="make" type="text" id="make" value="<?php echo $cd_make; ?>"></td>
			</tr>
			<tr> 
			<td width="100">Model</td>
			<td><input name="model" type="text" id="model" value="<?php echo $cd_model; ?>"></td>
			</tr>
			<tr> 
			<td width="100">Engine</td>
			<td><input name="engine" type="text" id="engine" value="<?php echo $cd_engine; ?>"></td>
			</tr>
			<tr> 
			<td width="100">Fuel Type</td>
			<td><input name="fuel_type" type="text" id="fuel_type" value="<?php echo $cd_fuel_type; ?>"></td>
			</tr>
			<tr> 
			<td width="100">Machine Type</td>
			<td><input name="body_type" type="text" id="body_type" value="<?php echo $cd_body_type; ?>"></td>
			</tr>
			<tr> 
			<td width="100">Year</td>
			<td><input name="year" type="text" id="year" size="4" maxlength="4" value="<?php echo $cd_year; ?>"></td>
			</tr>
			<tr> 
			<td width="100">Colour</td>
			<td><input name="colour" type="text" id="colour" value="<?php echo $cd_colour; ?>"></td>
			</tr>
			<tr> 
			<td width="100">Mileage</td>
			<td><input name="mileage" type="text" id="mileage" value="<?php echo $cd_mileage; ?>"></td>
			</tr>
			<tr> 
			<td width="100">Doors</td>
			<td><input name="doors" type="text" id="doors" size="1" maxlength="1" value="<?php echo $cd_doors; ?>"></td>
			</tr>
			<tr> 
			<td width="100">Owners</td>
			<td><input name="owners" type="text" id="owners" size="1" maxlength="1" value="<?php echo $cd_owners; ?>"></td>
			</tr>
			<tr> 
			<td width="100">Price</td>
			<td><input name="price" type="text" id="price" value="<?php echo $cd_price; ?>"></td>
			</tr>
			<tr> 
			<td width="100">Comments</td>
			<td><input name="comments" type="text" id="comments" value="<?php echo $cd_other_details; ?>"></td>
			</tr>
			<tr> 
			<td width="100">Photos Y/N</td>
			<td>
			<input type="radio" name="photo_yn" value="Y" <?php if ($cd_photo_yn=="Y"){echo "checked";} ?>> Yes
			<input type="radio" name="photo_yn" value="N" <?php if ($cd_photo_yn=="N"){echo "checked";} ?>> No								

			</td>
			</tr>							  
              <tr> 
                <td><input type="submit" name="Submit" value="Update Data"></td> 
                <td></td> 
              </tr> 				
            </table> 
          </form>

So i need the code <select name="status" id="status"> code above to be able to read the corresponding DB value.

And for the "New Car" form, again i need something similar, code that can generate a dropdown with the values from the db (ie: new & used) and on submit, save the selected value to db along with rest of info on this car.

Once this is done, i think all i have to do is write a select statment for each page "used.php" & "new.php" that pulls the required info from the db (already written) based on the cd_status value of "new" or "used".

Something like:

$sql="select * from dm_car_details where cd_status=new";

$sql="select * from dm_car_details where cd_status=used";

Sorry for it being so lonwinded,

Hope it makes sense.

Many thanks in advance

fugix · June 13, 2011

as for the select tag displaying the correct option for updating, you can try to use an if statement like so

<tr> 
			<td width="100">Machine Status</td>
			<td>
                
                <?php
                if($cd_status == "New"){?><select name="status" id="status">
			    <option value="new" selected="selected">New</option>
			    <option value="used">Used</option>
		      </select><?php } 
                 else if($cd_status == "Used") {?><select name="status" id="status">
			    <option value="new" >New</option>
			    <option value="used" selected="selected">Used</option>
		      </select><?php } ?>
                </td>
			</tr>

unrealindeed · June 13, 2011

Hi fugix,

Brilliant, that updates the field perfectly.

It makes total sense when I see it, but I could not for the life of me think how to write it.

Didn't realize an if else statement would do the trick.

I have created my new page now called "used.php" and am using the same code that generates the page content

<?php
require "db_connect.php";           // All database details will be included here 

$page_name="used.php"; //  If you use this code with a different page ( or file ) name then change this 

// Read the column name from query string. 


$paging_sort = $_GET["sort"];

$sort = $_GET["sort"];
if(isset($_GET["sort"]) && !empty($_GET["sort"])) {
  $sort = ($_GET["sort"]=="ASC") ? "DESC" : "ASC";
}
else{
  $sort = ($_GET["sort"]=="ASC") ? "DESC" : "ASC";  
}

$column_name=$_GET["column_name"];
if(isset($_GET["column_name"]) and strlen($column_name)>0){
$column_name=$_GET["column_name"]; 
}
else{
$column_name="cd_model";
}


$start=$_GET['start'];			    // To take care global variable if OFF
if(!($start > 0)) {                         // This variable is set to zero for the first page
$start = 0;
}

$eu = ($start - 0); 
$limit = 15;                                 // No of records to be shown per page.
$this1 = $eu + $limit; 
$back = $eu - $limit; 
$next = $eu + $limit; 


/////////////// WE have to find out the number of records in our table. We will use this to break the pages///////
$query2=" SELECT * FROM dm_car_details  ";
$result2=mysql_query($query2);
echo mysql_error();
$nume=mysql_num_rows($result2);
/////// The variable nume above will store the total number of records in the table////

/////////// Now let us print the table headers ////////////////
$bgcolor="#f1f1f1";
echo "<TABLE border =1 width=100% align=center  cellpadding=1 cellspacing=0> <tr>";
echo "<td width=30% bgcolor='B6C0FF' > <font face='verdana,helvetica' color='#000000' size='1'><a href='$page_name?column_name=cd_make&sort=$sort'>Description</a></font></td>";

echo "<td width=13% bgcolor='B6C0FF' align='center'> <font face='verdana,helvetica' color='#000000' size='1'><a href='$page_name?column_name=cd_body_type&sort=$sort'>Machine Type</a></font></td>";

echo "<td width=7% bgcolor='B6C0FF' align='center'> <font face='verdana,helvetica' color='#000000' size='1'><a href='$page_name?column_name=cd_photo_yn&sort=$sort'>Photo</a></font></td>";

echo "<td width=10% bgcolor='B6C0FF' align='center'> <font face='verdana,helvetica' color='#000000' size='1'><a href='$page_name?column_name=cd_year&sort=$sort'>Year</a></font></td>";

echo "<td width=10% bgcolor='B6C0FF' align='center'> <font face='verdana,helvetica' color='#000000' size='1'><a href='$page_name?column_name=cd_price&sort=$sort'>Price</a></font></td></tr>";

////////////// Now let us start executing the query with variables $eu and $limit  set at the top of the page///////////
$query=" SELECT * FROM dm_car_details order by $column_name $sort limit $eu, $limit  ";

$result=mysql_query($query);
echo mysql_error();

//////////////// Now we will display the returned records in side the rows of the table/////////
while($noticia = mysql_fetch_array($result))
{
if($bgcolor=='#f1f1f1'){$bgcolor='#ffffff';}
else{$bgcolor='#f1f1f1';}

echo "<tr >";
echo "<td width=30% align=left   bgcolor=$bgcolor id='title'> <font face='Verdana' size='1'><a href ='car_details.php?car_id=$noticia[cd_id]'>$noticia[cd_make] $noticia[cd_model]</font></td>"; 
echo "<td width=13% align=center bgcolor=$bgcolor id='title'> <font face='Verdana' size='1'>$noticia[cd_body_type]</font></td>"; 
if($noticia[cd_photo_yn]=='Y'){
echo "<td width=7% align=center  bgcolor=$bgcolor id='title'> <font face='Verdana' size='1'><img src=images/camera.gif></font></td>"; 
}
else{
echo "<td width=7% align=center  bgcolor=$bgcolor id='title'> <font face='Verdana' size='1'> </font></td>";
}

echo "<td width=5% align=center  bgcolor=$bgcolor id='title'> <font face='Verdana' size='1'>$noticia[cd_year]</font></td>"; 
echo "<td width=15% align=center bgcolor=$bgcolor id='title'> <font face='Verdana' size='1'>$noticia[cd_price]</font></td>"; 

echo "</tr>";
}
echo "</table>";
////////////////////////////// End of displaying the table with records ////////////////////////

/////////////// Start the buttom links with Prev and next link with page numbers /////////////////
echo "<table border=0 align = 'right' ><tr><td  align='right' >";
//// if our variable $back is equal to 0 or more then only we will display the link to move back ////////
if($back >=0) { 
print "<a href='$page_name?start=$back&column_name=$column_name&sort=$paging_sort'><font face='Verdana' size='1'>PREV</font></a>"; 
} 
//////////////// Let us display the page links at  center. We will not display the current page as a link ///////////
echo "</td><td align=center>";
$i=0;
$l=1;
for($i=0;$i < $nume;$i=$i+$limit){
if($i <> $eu){
echo " <a href='$page_name?start=$i&column_name=$column_name&sort=$paging_sort'><font face='Verdana' size='1'>$l</font></a> ";
}
else { echo "<font face='Verdana' size='2' color=red>$l</font>";}        /// Current page is not displayed as link and given font color red
$l=$l+1;
}


echo "</td><td  align='right'>";
///////////// If we are not in the last page then Next link will be displayed. Here we check that /////
if($this1 < $nume) { 
print "<a href='$page_name?start=$next&column_name=$column_name&sort=$paging_sort'><font face='Verdana' size='1'>NEXT</font></a>";} 
echo "</td></tr></table>";


?>

Am I right in saying alll i have to do is update

$query2=" SELECT * FROM dm_car_details  ";

with

$query2=" SELECT * FROM dm_car_details WHERE cd_status='used'";

and the same for my "new.php" page except changin "used" to "new" in my SELECT statement?

Many thanks

fugix · June 13, 2011

if you are looking to find the total number of rows for used and new cars, then yes your syntax is correct.

unrealindeed · June 13, 2011

Great,

That was a lot easier than I thought it would be.

Thank you so much for your help.

Unrealindeed

Sign In

What is the best Field Type for my Value

Recommended Posts

unrealindeed

Link to comment

Share on other sites

fugix

Link to comment

Share on other sites

unrealindeed

Link to comment

Share on other sites

fugix

Link to comment

Share on other sites

unrealindeed

Link to comment

Share on other sites

fugix

Link to comment

Share on other sites

unrealindeed

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information