Simple CMS

Hi friends,

I am trying to develop a simple CMS. I need some suggestion improve my code.

Database structure is like this


CREATE TABLE IF NOT EXISTS `city` (
  `id` int(5) NOT NULL AUTO_INCREMENT,
  `city` varchar(50) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `city` (`city`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=21 ;


CREATE TABLE IF NOT EXISTS `inspections` (
  `id` int(6) unsigned zerofill NOT NULL AUTO_INCREMENT,
  `insurers` int(3) NOT NULL,
  `issuing_office` varchar(150) NOT NULL,
  `city` int(3) NOT NULL,
  `insured_name` varchar(150) NOT NULL,
  `insured_add` varchar(255) NOT NULL,
  `insured_mob` varchar(30) NOT NULL,
  `insured_cont` varchar(30) NOT NULL,
  `vehicle_type` varchar(20) NOT NULL,
  `manufactrur` int(3) NOT NULL,
  `vehicle_make` varchar(50) NOT NULL,
  `vehicle_year` varchar(4) NOT NULL,
  `vehicle_no` varchar(30) NOT NULL,
  `engine_no` int(10) NOT NULL,
  `chassis_no` int(10) NOT NULL,
  `ins_type` varchar(20) NOT NULL,
  `available_at` varchar(200) NOT NULL,
  `agent_name` varchar(50) NOT NULL,
  `agent_mob` varchar(30) NOT NULL,
  `date_time` datetime NOT NULL,
  `surveyor_name` varchar(50) NOT NULL,
  `surveyor_mob` varchar(30) NOT NULL,
  `status_ins` varchar(30) NOT NULL,
  `status_report` varchar(15) NOT NULL,
  `recommendation` varchar(20) NOT NULL,
  `remarks` text NOT NULL,
  `created_by` varchar(20) NOT NULL,
  `uploaded_by` varchar(20) NOT NULL,
  `url` varchar(200) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=13 ;



CREATE TABLE IF NOT EXISTS `insurers` (
  `id` int(5) NOT NULL AUTO_INCREMENT,
  `insurers` varchar(150) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `insurers` (`insurers`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=13 ;


CREATE TABLE IF NOT EXISTS `manufactrur` (
  `id` int(5) NOT NULL AUTO_INCREMENT,
  `manufactrur` varchar(100) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `manufactrur` (`manufactrur`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;


CREATE TABLE IF NOT EXISTS `users` (
  `user_id` int(3) NOT NULL AUTO_INCREMENT,
  `user_name` varchar(20) NOT NULL,
  `user_password` varchar(50) NOT NULL,
  `user_email` varchar(50) NOT NULL,
  `user_rank` varchar(30) NOT NULL,
  `regdate` datetime NOT NULL,
  `last_login` datetime NOT NULL,
  PRIMARY KEY (`user_id`),
  UNIQUE KEY `user_name` (`user_name`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;


Tables City, Insurers & Manufactrurs are to populate dropdowns.
I have created three types of USER RANKS

  1. Admin
  2. Uploaders
  3. Insurers

First insurers will submit data into Inspections Table. Uploaders will receive an email when new record is inserted into Inspections Table. Then Uploaders will retreive data from inserted record & update/insert some addtion fields into Inspections Table & Make Folder on server & then upload images into that folder according to retreived record. Then Insurers will receive an emails about updated details & link to download upladed images. Functions to perform queries are \


function insurers ()
	{
	$query = " SELECT * FROM insurers ORDER BY insurers ASC" ;
	$result = mysql_query($query) ;
	echo '<select name="insurers" >' ;
	echo '<option value="">--Select--</option>';
	while ($ins = mysql_fetch_array($result))
		{
		echo '<option value="'.$ins['id'].'">'.$ins['insurers'].'</option>';
		}
	echo '</select>';
	return $insurers;	
	}

	
function city ()
	{
	$query = " SELECT * FROM city ORDER BY city ASC" ;
	$result = mysql_query($query) ;
	echo '<select name="city" >' ;
	echo '<option value="">--Select--</option>';
	while ($city = mysql_fetch_array($result))
		{
		echo '<option value="'.$city['id'].'">'.$city['city'].'</option>';
		}
	echo '</select>';
	return $city;
	}
	
	
function manufactrur ()
	{
	$query = " SELECT * FROM manufactrur ORDER BY manufactrur ASC" ;
	$result = mysql_query($query) ;
	echo '<select name="manufactrur" >' ;
	echo '<option value="">--Select--</option>';
	while ($manufactrur = mysql_fetch_array($result))
		{
		echo '<option value="'.$manufactrur['id'].'">'.$manufactrur['manufactrur'].'</option>';
		}
	echo '</select>';
	return $manufactrur;
	}
		

function validate ($value) 
	{
	$newVal = (!isset($value) || trim($value) == "") ? die ('Field cannot be left blank') : 
	mysql_real_escape_string(trim($value));
	return $newVal;
	}
	
	
function newIntimations ()
	{
	$query =  " SELECT inspections.id, insurers.insurers, inspections.vehicle_no, 
				DATE_FORMAT(inspections.date_time, '%d-%b-%Y %r') as date_time
				FROM inspections, insurers
				WHERE inspections.status_ins = 'PENDING'
				AND inspections.insurers = insurers.id
				ORDER BY inspections.id DESC" ;
				
		$result = mysql_query( $query );
		if (!$result)
		{
		die ("Could not query the database: <br />". mysql_error( ));
		}
		
		echo "<table border=1 > 
			  <tr>
			  <td><b>Sr No</b></td>
			  <td><b>Ref No</b></td>
			  <td><b>Insurers</b></td>
			  <td><b>Vehicle No</b></td>
			  <td><b>Date/Time</b></td>
			  </tr>";
		$i = 0 ;
		while  ($row = mysql_fetch_array($result,MYSQL_ASSOC)) 
			{
			$i++;
			echo   "<tr><td>" . $i . "<td><a href='details.php?id=" . $row['id'] ."&vehicle_no=" . $row['vehicle_no'] . "'>" . $row['id'] .  "</a></td><td>"
				. $row['insurers'] . '</td><td>' 		  
				. $row['vehicle_no'] . '</td><td>'
				. $row['date_time'] . '</td></tr>';
			}
			echo '</table>';
	}

Trying to use Queued Photo Uploader

http://digitarald.de/project/fancyupload/

What are you looking to improve exactly?

By looking at db scheme my suggestions would be: change charset to utf-8, engine to InnoDB and add foreign keys.

As for the PHP code you could use escape content you are displaying ir. check out htmlentities() or htmlspecialchars() functions.

Hope that helps.