Jump to content

What is the best Field Type for my Value

unrealindeed

By unrealindeed
in MySQL Help

 Share

Recommended Posts

unrealindeed Newbie

unrealindeed

Posted
Posted

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]

Link to comment
Share on other sites
fugix Member

fugix

Posted
Posted

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.

Link to comment
Share on other sites
unrealindeed Newbie

unrealindeed

Posted
  • Author
Posted

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

Link to comment
Share on other sites
fugix Member

fugix

Posted
Posted

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>

 

Link to comment
Share on other sites
unrealindeed Newbie

unrealindeed

Posted
  • Author
Posted

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

Link to comment
Share on other sites
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.