Van mysql naar mysqli - Forum

Theo Luwema

20-01-2017 15:04

- Ariën - op 20/01/2017 14:49:52

We kunnen ook via het forum helpen, maar als je het wilt laten doen door iemand anders dan ben je vrij om een vacature in het Vacatureforum te plaatsen.

Ik zou het graag via het forum doen Ariën,alleen weet ik dan niet goed waar ik moet beginnen.........

Ariën

20-01-2017 15:44

Vertel eens waarc je precies op vast loopt. Krijg je foutmeldingen? Laat de betreffende relevante code eens zien?

T

Theo Luwema

20-01-2017 16:50 gewijzigd op 21-01-2017 19:20

code php


<?php
error_reporting(E_ALL); 
ini_set( 'display_errors','1');
	include 'header.php';
	include 'db/db_connect.php';
	
$table    = "dog";    // Der Name der Datenbanktabelle
// Datendefinition für die Clientausgabe
$start = $_GET['start'];
$start = (isset($start)) ? abs((int)$start) : 0;
$limit = 50;                     // Datensätze pro Ausgabeseite

// Feststellen der Anzahl der verfügbaren Datensätze.
//$resultID = @mysql_query("SELECT COUNT(ID) FROM ".$table);
//$total    = @mysql_result($resultID,0);

// Ggf. $start korrigieren (falls Parameter in
// der URL manipuliert wurde)
//$start    = ($start >= $total) ? $total - $limit : $start;

$mode = $_GET['mode'];
$searchterm = $_GET['keyword'];
$sortBy = mysqli_real_escape_string($connect, $_GET['sortBy']);
$sortDir = mysqli_real_escape_string($connect, $_GET['sortDir']);
if (!$sortBy) 
	$sortBy="name";
if (!$sortDir) 
	$sortDir="ASC";
?>


<div class="main">
<div class="content">

<?
if ($mode == "simple") {
	$searchterm = strtr($searchterm,"´`’","'''");
	$searchterm = escape_smart($searchterm);
	$prequery = "SELECT COUNT(ID) FROM ".$table." WHERE name LIKE '%$searchterm%'";
	$preresult = mysqli_query($connect, $prequery) or die('Query failed: ' . mysqli_error($connect));	
	$total = mysqli_result($connect, $preresult,0);
	// Ggf. $start korrigieren (falls Parameter in der URL manipuliert wurde)
	//$start    = ($start >= $total) ? $total - $limit : $start;
	$query = "SELECT id,name,sex,yearofbirth,landofbirth,landofstanding,color,A5b_Studstatus,sire_id,dam_id,length($sortBy)/length($sortBy) AS len FROM dog WHERE name LIKE '%$searchterm%' ORDER BY len DESC,$sortBy $sortDir, name LIMIT ".$start.",".$limit;
	$result = mysqli_query($connect, $query) or die('Query failed: ' . mysqli_error($connect));	
	$num_result = mysqli_num_rows($result);
} elseif ($mode == "advanced") {
	// construct complex query	
	if ($_GET['photos'])
		$query = "SELECT DISTINCT photos_dog.dog_id, dog.id,dog.name,dog.sex,dog.yearofbirth,dog.landofbirth,landofstanding,dog.color,A5b_Studstatus,dog.sire_id,dog.dam_id,length($sortBy)/length($sortBy) AS len FROM dog, photos_dog";
	else
		$query = "SELECT dog.id,dog.name,dog.sex,dog.yearofbirth,dog.landofbirth,landofstanding,dog.color,A5b_Studstatus,dog.sire_id,dog.dam_id,length($sortBy)/length($sortBy) AS len FROM dog";
	$clauses = array();
	$i=0;
	if ($_GET['name']) {
 	 $name = strtr($name,"´`’","'''");	
	 $name = escape_smart($_GET['name']);
	 $clauses[$i] = " dog.name LIKE '%$name%'";
	 $i++;
	}
	if ($_GET['nickname']) {
 	 $nickname = escape_smart($_GET['nickname']);
	 $clauses[$i] = "dog.nickname LIKE '%$nickname%'";
	 $i++;
	}
	if ($_GET['sex']) {
	 $sex = escape_smart($_GET['sex']);	
	 $clauses[$i] = " dog.sex = '$sex'";
	 $i++;
	 	}
	if ($_GET['A5b_Studstatus']) {
	 $A5b_Studstatus = escape_smart($_GET['A5b_Studstatus']);	
	 $clauses[$i] = " dog.A5b_Studstatus = '$A5b_Studstatus'";
	 $i++;
	}
	$year = escape_smart($_GET['byear']);
	if ($year) {
	 if ($year == "1900")
		 $clauses[$i] = " dog.yearofbirth < 1900";
	 else if ($year == "1950")
		 $clauses[$i] = " dog.yearofbirth < 1950 AND dog.yearofbirth >= 1900";
	 else
 		 $clauses[$i] = " dog.yearofbirth LIKE '%$year%'";
	 $i++;
	}
	if ($_GET['landofbirth']) {
	 $landofbirth = escape_smart($_GET['landofbirth']);
	 $clauses[$i] = " dog.landofbirth='$landofbirth'";
	 $i++;
	}
	if ($_GET['landofstanding']) {
	 $landofstanding = escape_smart($_GET['landofstanding']);
	 $clauses[$i] = " dog.landofstanding='$landofstanding'";
	 $i++;
	}
	if ($_GET['color']) {
	 $color = escape_smart($_GET['color']);
	 $clauses[$i] = " dog.color LIKE '%$color%'";
	 $i++;
	}
	if ($_GET['kennel']) {
	 $kennel = escape_smart($_GET['kennel']);
	 $clauses[$i] = " dog.kennel LIKE '%$kennel%'";
	 $i++;
	}
		if ($_GET['E1a_HDresult']) {
	 $E1a_HDresult = escape_smart($_GET['E1a_HDresult']);	
	 $clauses[$i] = " dog.E1a_HDresult = '$E1a_HDresult'";
	 $i++;
	}
	if ($_GET['E2a_xrayEDresult']) {
	 $E2a_xrayEDresult = escape_smart($_GET['E2a_xrayEDresult']);	
	 $clauses[$i] = " dog.E2a_xrayEDresult = '$E2a_xrayEDresult'";
	 $i++;
	}
	if ($_GET['K1_ductus_botalli']) {
	 $K1_ductus_botalli = escape_smart($_GET['K1_ductus_botalli']);	
	 $clauses[$i] = " dog.K1_ductus_botalli = '$K1_ductus_botalli'";
	 $i++;
	}
    
	// not in the dog table!!
/*	if ($_GET['breeder']) {
	 $breeder = escape_smart($_GET['breeder']]);
	 $clauses[$i] = " breeder LIKE '%$breeder%'";
	 $i++;
	}
	if ($_GET['owner']) {
	 $owner = escape_smart($_GET['owner']]);	
	 $clauses[$i] = " owner LIKE '%$owner%'";
	 $i++;
	}*/
	if ($_GET['sire_id']) {
	 $clauses[$i] = " dog.sire_id='(int)$_GET[sire_id]'";
	 $i++;
	}
	if ($_GET['dam_id']) {
	 $clauses[$i] = " dog.dam_id='(int)$_GET[dam_id]'";
	 $i++;
	}
	if ($_GET['photos']) {
		$clauses[$i] = " dog.id=photos_dog.dog_id";
	 	$i++;
	}
	if (count($clauses)) {
		$query = "$query WHERE $clauses[0]";
		for ($j=1;$j<$i;$j++) {
			$query = "$query AND $clauses[$j]"; 
		}
	}	
	$query = "$query ORDER BY len desc,$sortBy $sortDir, dog.name  ";
	$preresult = mysqli_query($connect, $query) or die('Query failed: ' . mysqli_error($connect));	
	$total = mysqli_num_rows($preresult);
	// Ggf. $start korrigieren (falls Parameter in der URL manipuliert wurde)
	//$start    = ($start >= $total) ? $total - $limit : $start;
	$query = "$query LIMIT ".$start.",".$limit;
	$result = mysqli_query($connect, $query) or die('Query failed: ' . mysqli_error($connect));
}
?>

<!-- display results in table -->
	<div align="left" style="padding:10px">
<?
	if ($total == 0) {
		echo "There were <b>no Schapendoes</b> found matching your search criteria.";	
		echo "</div>";
	}	else {
		if ($total == 1) 
			echo "There was <b>1 dog</b> found matching your search criteria.";
	 	else 
			echo "There were <b>$total Schapendoes</b> found matching your search criteria. ";
		echo "Displaying <b>", $start + 1, "-";
		if ($start+$limit < $total)
			echo $start + $limit; 
		else
			echo $total;
		echo " of $total</b>";
?>
	</div>
	<p>		
			<? /*$searchcriteria = "&name=$_GET[name]&nickname=$_GET[nickname]&sex=$_GET[sex]&A5b_Studstatus=$_GET[A5b_Studstatus]&dateofbirth=$_GET[dateofbirth]&
			landofbirth=$_GET[landofbirth]&landofstanding=$_GET[landofstanding]&color=$_GET[color]&kennel=$_GET[kennel]&
			breeder=$_GET[breeder]&owner=$_GET[owner]&sire=$_GET[sire_id]&dam=$_GET[dam_id]";*/
			foreach ($_GET as $key => $value) {
					if (($key != "sortBy") && ($key != "sortDir"))
						$searchcriteria = "$searchcriteria&$key=$value"; 
			}
			//echo $searchcriteria;
			?>

		<table id="doglist" cellpadding="5px">
			<tr>
				<th>name
					<a href="results.php?sortBy=name&sortDir=ASC<? echo $searchcriteria ?>">
						<? if (($sortBy == "name") && ($sortDir == "ASC")) $icon = "images/sort_asc_active.gif";
							 else	$icon = "images/sort_asc.gif"; ?>
						<img src="<? echo $icon ?>">
					</a>
					<a href="results.php?sortBy=name&sortDir=DESC<? echo $searchcriteria ?>">
						<? if (($sortBy == "name") && ($sortDir == "DESC")) $icon = "images/sort_desc_active.gif";
							 else	$icon = "images/sort_desc.gif"; ?>
						<img src="<? echo $icon ?>">					
					</a>				
				</th>
				<th>sex
					<a href="results.php?sortBy=sex&sortDir=ASC<? echo $searchcriteria ?>">
						<? if (($sortBy == "sex") && ($sortDir == "ASC")) $icon = "images/sort_asc_active.gif";
							 else	$icon = "images/sort_asc.gif"; ?>
						<img src="<? echo $icon ?>">					
					</a>
					<a href="results.php?sortBy=sex&sortDir=DESC<? echo $searchcriteria ?>">
						<? if (($sortBy == "sex") && ($sortDir == "DESC")) $icon = "images/sort_desc_active.gif";
							 else	$icon = "images/sort_desc.gif"; ?>
						<img src="<? echo $icon ?>">	
					</a>				
				</th>
				<th>sire
					<a href="results.php?sortBy=sire_id&sortDir=ASC<? echo $searchcriteria ?>">
						<? if (($sortBy == "sire_id") && ($sortDir == "ASC")) $icon = "images/sort_asc_active.gif";
							 else	$icon = "images/sort_asc.gif"; ?>
						<img src="<? echo $icon ?>">					
					</a>
					<a href="results.php?sortBy=sire_id&sortDir=DESC<? echo $searchcriteria ?>">
						<? if (($sortBy == "sire_id") && ($sortDir == "DESC")) $icon = "images/sort_desc_active.gif";
							 else	$icon = "images/sort_desc.gif"; ?>
						<img src="<? echo $icon ?>">						
					</a>				
				</th>
				<th>dam
					<a href="results.php?sortBy=dam_id&sortDir=ASC<? echo $searchcriteria ?>"> 
						<? if (($sortBy == "dam_id") && ($sortDir == "ASC")) $icon = "images/sort_asc_active.gif";
							 else	$icon = "images/sort_asc.gif"; ?>
						<img src="<? echo $icon ?>">										
					</a>
					<a href="results.php?sortBy=dam_id&sortDir=DESC<? echo $searchcriteria ?>">
						<? if (($sortBy == "dam_id") && ($sortDir == "DESC")) $icon = "images/sort_desc_active.gif";
							 else	$icon = "images/sort_desc.gif"; ?>
						<img src="<? echo $icon ?>">						
					</a>				
				</th>
				<th>colour
					<a href="results.php?sortBy=color&sortDir=ASC<? echo $searchcriteria ?>"> 
						<? if (($sortBy == "color") && ($sortDir == "ASC")) $icon = "images/sort_asc_active.gif";
							 else	$icon = "images/sort_asc.gif"; ?>
						<img src="<? echo $icon ?>">					
					</a>
					<a href="results.php?sortBy=color&sortDir=DESC<? echo $searchcriteria ?>">
						<? if (($sortBy == "color") && ($sortDir == "DESC")) $icon = "images/sort_desc_active.gif";
							 else	$icon = "images/sort_desc.gif"; ?>
						<img src="<? echo $icon ?>">						
					</a>
				</th>
				<th>birth year
					<a href="results.php?sortBy=yearofbirth&sortDir=ASC<? echo $searchcriteria ?>">
						<? if (($sortBy == "yearofbirth") && ($sortDir == "ASC")) $icon = "images/sort_asc_active.gif";
							 else	$icon = "images/sort_asc.gif"; ?>
						<img src="<? echo $icon ?>">					
					</a>
					<a href="results.php?sortBy=yearofbirth&sortDir=DESC<? echo $searchcriteria ?>">
						<? if (($sortBy == "yearofbirth") && ($sortDir == "DESC")) $icon = "images/sort_desc_active.gif";
							 else	$icon = "images/sort_desc.gif"; ?>
						<img src="<? echo $icon ?>">					
					</a>
				</th>
			</tr>
			
			<?php
			$i = 0; 
			while ($line = mysqli_fetch_object($result)) {
				if (($line->sire_id == null) || ($line->dam_id == null) || ($line->landofbirth == null) || ($line->yearofbirth == null)) {
					$imgIcon = 'images/TWA_logo_square_orange_14px.gif';
					$tooltipText = 'Important info missing';
				} else {
					$imgIcon = 'images/TWA_logo_square_green_14px.gif'; 
					$tooltipText = 'Most important info available';
				} ?>
			<tr <? if ($i%2) { ?> style="background-color:#ECF3F7;" <? } ?> >
				<td> <img src='<? echo $imgIcon ?>' onmouseover="Tip('<? echo $tooltipText ?>', WIDTH, 170, BGCOLOR, '#CADCEB', BORDERCOLOR, '#115098',
							FONTCOLOR,'#354A6A',FONTFACE,'verdana,geneva,sans-serif',FONTSIZE,'10px', SHADOW, true, 
							SHADOWCOLOR,'#115098', SHADOWWIDTH,3,PADDING,2)" weight='14px' height="14px"/>
					<a href="details.php?id=<?php echo $line->id;?>"><? echo $line->name;?>
					<?
					$q="SELECT photo_id FROM photos_dog WHERE dog_id=$line->id";
					$r = mysqli_query($connect, $q) or die('Query failed: ' . mysqli_error($connect));
					if ($l = mysqli_fetch_object($r)) {		
						if ($l != null)
					?>
							<img src='images/camera.gif' style='opacity: .8;filter: alpha(opacity=80);' 
							onmouseover="Tip('photo available', WIDTH, 95, BGCOLOR, '#CADCEB', BORDERCOLOR, '#115098',
							FONTCOLOR,'#354A6A',FONTFACE,'verdana,geneva,sans-serif',FONTSIZE,'10px', SHADOW, true, 
							SHADOWCOLOR,'#115098', SHADOWWIDTH,3,PADDING,2)"/>
				<?php } ?>
					</a>
				</td>
					<?php 
							echo "<td> $line->sex </td>";
							// get sire and dam names
							echo "<td>";
							if ($line->sire_id) {
								$r = mysqli_query($connect, "SELECT name FROM dog WHERE id=$line->sire_id") or die ('Query failed: ' . mysqli_error($connect));
								$l = mysqli_fetch_object($r);
								echo $l->name;
							}
							echo "</td><td>";
							if ($line->dam_id) {							
								$r = mysqli_query($connect, "SELECT name FROM dog WHERE id=$line->dam_id") or die ('Query failed: ' . mysqli_error($connect));
								$l = mysqli_fetch_object($r);
								echo $l->name;							
							}
							echo "</td>";
							echo "<td> $line->color </td>"; 
							echo "<td> $line->yearofbirth </td>"; 
					?>
			</tr>
			<?php
				$i++;
			}
			?>			
		</table>
		<table width="95%"><tr><td width="40%" align="right">
		<?
		// Zurück- und Vorblättern
		if ($start > 0) {
			$newStart = ($start - $limit < 0) ? 0 : ($start-$limit);
			echo "<label><a href=".$_SERVER['PHP_SELF']."?".$_SERVER['QUERY_STRING']."&start=".$newStart."><strong>&lt;&lt; previous</strong></a></label>";
		}
		?>			
		</td><td width="20%">&nbsp;&nbsp;</td><td align="left" width="40%">
		<?
		if ($start + $limit < $total) {
			$newStart = $start + $limit;
			echo "<label><a href=".$_SERVER['PHP_SELF']."?".$_SERVER['QUERY_STRING']."&start=".$newStart."><strong>next &gt;&gt;</strong></a></label>";
		}
			?>	
		</td></tr></table>
	</p>
	<?
	} ?>
</div>
</div>

<?php 
	include 'footer.php';
	
	function escape_smart($value) {
		// stripslashes, if necessary
		if (get_magic_quotes_gpc()) {
			$value = stripslashes($value);
		}
		// escape, if no integer
		if (!is_numeric($value)) {
			$value = mysqli_real_escape_string($value, $connect);
		}
		return $value;
	}
	
?>

[size=xsmall]Toevoeging op 20/01/2017 16:52:24:[/size]

$value = mysqli_real_escape_string($value, $connect);

Op deze regel gaat het mis
Warning: mysqli_real_escape_string() expects parameter 1 to be mysqli, string given in /home/vhosting/d/vhost0050361/domains/lutee.nl/htdocs/www/results.php on line 368

Ivo P

20-01-2017 17:05

De relevante code was regels 365 / 370 geweest, aangezien je een foutmelding hebt op regel 368

Namelijk: er staat als eerste argument van je functie een string. en daar had een variabele van het type mysqli (de connectie) moeten staan.

[size=xsmall]Toevoeging op 20/01/2017 17:07:39:[/size]

en het zou zo maar kunnen dat dat over die mysqli_real_escape_functie gaat helemaal onderaan.

magic_quotes bestaat gelukkig al heel lang niet meer, dus je kunt ipv de aanroep naar smart_quotes ook direct daar mysqli_real_escape_string gebruiken...

T

Theo Luwema

21-01-2017 09:21 gewijzigd op 21-01-2017 09:24

Het stukje waar ik nu mee bezig ben is een zoekfunctie.
Er zijn twee zoekfunctie's :
simple en advanced.
Bij de simpele zoekopdracht gaat het ook fout in de onderste sectie.

Warning: mysqli_real_escape_string() expects parameter 1 to be mysqli, null given in /home/vhosting/d/vhost0050361/domains/lutee.nl/htdocs/www/results.php on line 368

code php
<?php

function escape_smart($value) {
// stripslashes, if necessary
if (get_magic_quotes_gpc()) {
$value = stripslashes($value);
}
// escape, if no integer
if (!is_numeric($value)) {
$value = mysqli_real_escape_string($connect, $value);
}
return $value;
}
?>
code php

$value = mysqli_real_escape_string($connect, $value);

ik begrijp niet wat ik hier fout doe

Ariën

21-01-2017 11:36

De hele functie escape_smart() zou je kunnen schrappen, omdat Magic Quotes al tijden niet meer in PHP zitten. Je kan beter direct de mysqli_real_escape_string() functie gebruiken bij het opstellen van je query.

Frank Nietbelangrijk

21-01-2017 16:23

moderators: kan de bovenstaande code niet even tussen code tags gezet worden?

[size=xsmall]Toevoeging op 21/01/2017 16:25:29:[/size]

Waarom begin je direct met je output terwijl je nog niet weet hoe het verloop van je applicatie is?
(Volgens mij heb je dit advies al eerder gekregen?)

Ariën

21-01-2017 19:20

Code-tags heb ik er nu omheen gezet.

Frank Nietbelangrijk

21-01-2017 23:35

<?php
$value = mysqli_real_escape_string($value, $connect);
?>

Moet volgens mij zijn:
<?php

$value = mysqli_real_escape_string($connect, $value);
?>

Vervolgens bestaat die $connect niet binnen je functie escape_smart(). Dus zou je de functie moeten uitbreiden met een extra parameter:
<?php
function escape_smart($value, $connect)
{
//...
}
?>

en dan bij het aanroepen van de functie natuurlijk ook de tweede parameter meegeven