Scripts
Visuele mysql-voorwaarden

Door Jelmer - -16-01-2006 00:00 -5.505 views
Een simpel scriptje, geïnspireerd door de smart playlists van iTunes. Ik denk dat de demo voor zichzelf spreekt. De database in het voorbeeld is een met zo ongeveer 10.000 items uit World of Warcraft maar met een limit op 30. Misschien dat ik order en limit nog eens erin bouw, net als lussen.
Download alle bestanden als ZIP
visuele-mysqlvoorwaarden
[code]<?php
//deze twee regels zijn even quick en dirty, ik neem aan dat je dit allemaal zelf in je script mooi afhandeld.
mysql_connect('localhost', 'root', '');
mysql_select_db('1130_wow');

$columns = array();

//alle mogelijkheden definieren, ook nodig voor de controle achteraf.
$res = mysql_query("SHOW COLUMNS FROM items");
for($i=0;$i<mysql_num_rows($res);$i++){
   array_push($columns, mysql_result($res, $i));
}

$selectors = array(
	'=' => 'gelijk aan',
	'>=' => 'groter of gelijk aan',
	'<=' => 'kleiner of gelijk aan',
	'>' => 'groter dan',
	'<' => 'kleiner dan',
	'!=' => 'niet gelijk aan',
	'<>' => 'alles behalve',
	'LIKE' => 'als (gebruik % in waarde)');
$controlers = array(
	'AND' => 'en',
	'OR' => 'of',
	'XOR' => 'of alleen');

        //quote alles behalve tabelnamen (kan je velden combineren) en integers.
	function quote($value)
	{
		global $columns;
		if (get_magic_quotes_gpc()) {
			$value = stripslashes($value);
		}
		
		if (!is_int($value) && !is_numeric($value) && !in_array($value, $columns)) {
			$value = '"' . mysql_real_escape_string($value) . '"';
		}
		
		return $value;
	}
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
	"http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html>
	<head>
		<title>Smart Query Maker</title>
		<style type="text/css">
			*{
				font-family: Verdana;
				font-size: 11px;
			}
			h1{
				font-size: 18px;
			}
			.separator{
				width: 100%;
				display: block;
				text-align: center;
				background: transparent url('./1black.gif') center center repeat-x;
			}
			.query{
				width: 100%;
				display: block;
				margin: 0;
			}
			.query .key{
				width: 29%;
			}
			.query .selector{
				width: 20%;
			}
			.query .value{
				width: 49%;
			}
			#controls input{
				float: right;
			}
			table{
				width: 100%;
				border-collapse: collapse;
				border-spacing: 0;
			}
			th{
				border-bottom: 1px solid black;
				font-weight: bold;
			}
			td{
				border-left: 1px solid black;
			}
			tr.oneven td{
				background-color: #99CCCC;
			}
			td.message{
				font-size: 20px;
				text-align: center;
				border-left: 0;
			}
		</style>
		
		<script>
			var columns = new Array(
			<?php 
				$tmp = array();
				for($i = 0; $i < count($columns); $i++)
				{
					array_push($tmp, "'".$columns[$i]."'");
				}
				echo implode(', ', $tmp); 
			?>);
			var selectors = new Array(
			<?php 
				$tmp = array();
				foreach($selectors as $key => $value){
					array_push($tmp, "new Array('".$key."', '".$value."')");
				}
				echo implode (', ', $tmp);
			?>);
			var values = null;
			var controlers = new Array(
			<?php 
				$tmp = array();
				foreach($controlers as $key => $value){
					array_push($tmp, "new Array('".$key."', '".$value."')");
				}
				echo implode (', ', $tmp);
			?>);
						
			function addSelector()
			{
				var currentSelectors = document.getElementById('selectorLine').childNodes.length;
					if(currentSelectors > 0)
					{
						addControler();
					}
				var wrapper = document.createElement('div');
					wrapper.id = 'wrapper_'+currentSelectors;
					wrapper.className = 'query';
				
				var keySelector = document.createElement('select');
					keySelector.name = 'key[]';
					keySelector.className = 'key';
					for(i = 0; i < columns.length; i++)
					{
						var option = document.createElement('option');
							option.value = columns[i];
							keySelector.appendChild(option);
						var optionLabel = document.createTextNode(columns[i]);
							option.appendChild(optionLabel);
					}
					wrapper.appendChild(keySelector);
				
				var selectorSelector = document.createElement('select');
					selectorSelector.name = 'selector[]';
					selectorSelector.className = 'selector';
					for(i = 0; i < selectors.length; i++)
					{
						var option = document.createElement('option');
							option.value = selectors[i][0];
							selectorSelector.appendChild(option);
						var optionLabel = document.createTextNode(selectors[i][1]);
							option.appendChild(optionLabel);
					}
					wrapper.appendChild(selectorSelector);
				
				var valueSelector = document.createElement('input');
					valueSelector.name='value[]';
					valueSelector.className = 'value';
					wrapper.appendChild(valueSelector);
					
					document.getElementById('selectorLine').appendChild(wrapper);
				
				return;
			}
			
			function addControler()
			{
				var separator = document.createElement('div');
					separator.className = 'separator';
				var controlerSelector = document.createElement('select');
					controlerSelector.name = 'controler[]';
					for(i= 0; i < controlers.length; i++)
					{
						var option = document.createElement('option');
							option.value = controlers[i][0];
						var optionLabel = document.createTextNode(controlers[i][1]);
							option.appendChild(optionLabel);
						controlerSelector.appendChild(option);
					}
					separator.appendChild(controlerSelector);
				document.getElementById('selectorLine').appendChild(separator);
				return;
			}
			
			function removeSelector()
			{
				if(document.getElementById('selectorLine').childNodes.length < 2)
				{
					return false;
				}
				
				//2 lijnen verwijderen, daarom 2 maal aanroepen
				document.getElementById('selectorLine').removeChild(document.getElementById('selectorLine').lastChild);
				document.getElementById('selectorLine').removeChild(document.getElementById('selectorLine').lastChild);
				
				return;
			}
			
			window.onload = addSelector;
		</script>
	</head>
	<body>
		<div id="wrapper">
			<h1>Query Selector</h1>
			<form method="post" action="#">
				<fieldset>
					<legend>Voorwaarde</legend>
					<div id="selectorLine"></div>
					<div id="controls">
						<input type="button" value="+" onclick="addSelector()"/>
						<input type="button" value="-" onclick="removeSelector(null)"/>
					</div>
				</fieldset>
				<input type="submit"/>
			</form>
		</div>
		<?php
		if($_SERVER['REQUEST_METHOD'] == 'POST')
		{
			echo '<h1>Resultaat</h1>';
			$illegal = false;
			echo 'Query is: ';
			$query = 'SELECT * FROM items WHERE ';
			for($i = 0; $i < count($_POST['key']); $i++)
			{
				if(!in_array($_POST['key'][$i], $columns)){						$illegal = true;}
				if(!in_array($_POST['selector'][$i], array_keys($selectors))){	$illegal = true;}

				$query .= $_POST['key'][$i] . ' ' . $_POST['selector'][$i] . ' ' . quote($_POST['value'][$i]);
				if(isset($_POST['controler'][$i]))
				{
					if(!in_array($_POST['controler'][$i], array_keys($controlers))){	$illegal = true;}
					$query .= ' '.$_POST['controler'][$i].' ';
				}
			}
			echo $query.'<br/>';
			echo 'de opgestuurde data is trouwens ', ($illegal) ? 'niet geldig en daarom niet uitgevoerd.' : 'geldig.', '<br/>';
/****** vanaf hier is het alleen nog maar mysql uitvoeren, met alleen demo-doeleinden *****/
			if(!$illegal)
			{
				$sql_results = mysql_query($query.' LIMIT 0,30') or print(mysql_error());
				if($sql_results){
					$rows = array();
					$headers = false;
					$i = 0;
					echo '<table>';
					while($row = mysql_fetch_assoc($sql_results))
					{
						if($i%2 == 0) $tablerow = '<tr class="oneven">';
						else       $tablerow = '<tr>';
						foreach($row as $key => $value)
						{
							if(!$headers)
							{
								echo '<th>'.htmlentities($key).'</th>';
							}
							$tablerow .= '<td>'.htmlentities($value).'</td>';
						}
						$headers = true;
						$tablerow .= '</tr>';
						
						array_push($rows, $tablerow);
						$i++;
					}
					if(mysql_num_rows($sql_results) < 1)
					{
						echo '<tr><td class="message">Geen resultaten gevonden</tr><tr>';
					}
					echo implode("\n", $rows);
					echo '</table>';
				}
			}
		}
		?>
	</body>
</html>
[/code]