library/adodb/tests/test4.php

   1 <?php
   2
   3 /**
   4  * @version V4.50 6 July 2004 (c) 2000-2011 John Lim (jlim#natsoft.com). All rights reserved.
   5  * Released under both BSD license and Lesser GPL library license.
   6  * Whenever there is any discrepancy between the two licenses,
   7  * the BSD license will take precedence.
   8  *
   9  * Set tabs to 4 for best viewing.
  10  *
  11  * Latest version is available at http://php.weblogs.com
  12  *
  13  * Test GetUpdateSQL and GetInsertSQL.
  14  */
  15
  16 error_reporting(E_ALL);
  17 function testsql()
  18 {
  19
  20
  21 include('../adodb.inc.php');
  22 include('../tohtml.inc.php');
  23
  24 global $ADODB_FORCE_TYPE;
  25
  26
  27 //==========================
  28 // This code tests an insert
  29
  30 $sql = "
  31 SELECT *
  32 FROM ADOXYZ WHERE id = -1";
  33 // Select an empty record from the database
  34
  35
  36 #$conn = ADONewConnection("mssql");  // create a connection
  37 #$conn->PConnect("", "sa", "natsoft", "northwind"); // connect to MySQL, testdb
  38
  39 $conn = ADONewConnection("mysql");  // create a connection
  40 $conn->PConnect("localhost", "root", "", "test"); // connect to MySQL, testdb
  41
  42
  43 #$conn = ADONewConnection('oci8po');
  44 #$conn->Connect('','scott','natsoft');
  45
  46 if (PHP_VERSION  >= 5) {
  47         $connstr = "mysql:dbname=northwind";
  48         $u = 'root';$p='';
  49         $conn = ADONewConnection('pdo');
  50         $conn->Connect($connstr, $u, $p);
  51 }
  52 //$ADODB_FETCH_MODE = ADODB_FETCH_ASSOC;
  53
  54
  55 $conn->debug=1;
  56 $conn->Execute("delete from adoxyz where lastname like 'Smi%'");
  57
  58 $rs = $conn->Execute($sql); // Execute the query and get the empty recordset
  59 $record = array(); // Initialize an array to hold the record data to insert
  60
  61 if (strpos($conn->databaseType,'mysql')===false) $record['id'] = 751;
  62 $record["firstname"] = 'Jann';
  63 $record["lastname"] = "Smitts";
  64 $record["created"] = time();
  65
  66 $insertSQL = $conn->GetInsertSQL($rs, $record);
  67 $conn->Execute($insertSQL); // Insert the record into the database
  68
  69 if (strpos($conn->databaseType,'mysql')===false) $record['id'] = 752;
  70 // Set the values for the fields in the record
  71 $record["firstname"] = 'anull';
  72 $record["lastname"] = "Smith\$@//";
  73 $record["created"] = time();
  74
  75 if (isset($_GET['f'])) $ADODB_FORCE_TYPE = $_GET['f'];
  76
  77 //$record["id"] = -1;
  78
  79 // Pass the empty recordset and the array containing the data to insert
  80 // into the GetInsertSQL function. The function will process the data and return
  81 // a fully formatted insert sql statement.
  82 $insertSQL = $conn->GetInsertSQL($rs, $record);
  83 $conn->Execute($insertSQL); // Insert the record into the database
  84
  85
  86
  87 $insertSQL2 = $conn->GetInsertSQL($table='ADOXYZ', $record);
  88 if ($insertSQL != $insertSQL2) echo "<p><b>Walt's new stuff failed</b>: $insertSQL2</p>";
  89 //==========================
  90 // This code tests an update
  91
  92 $sql = "
  93 SELECT *
  94 FROM ADOXYZ WHERE lastname=".$conn->Param('var'). " ORDER BY 1";
  95 // Select a record to update
  96
  97 $varr = array('var'=>$record['lastname'].'');
  98 $rs = $conn->Execute($sql,$varr); // Execute the query and get the existing record to update
  99 if (!$rs || $rs->EOF) print "<p><b>No record found!</b></p>";
 100
 101 $record = array(); // Initialize an array to hold the record data to update
 102
 103
 104 // Set the values for the fields in the record
 105 $record["firstName"] = "Caroline".rand();
 106 //$record["lasTname"] = ""; // Update Caroline's lastname from Miranda to Smith
 107 $record["creAted"] = '2002-12-'.(rand()%30+1);
 108 $record['num'] = '';
 109 // Pass the single record recordset and the array containing the data to update
 110 // into the GetUpdateSQL function. The function will process the data and return
 111 // a fully formatted update sql statement.
 112 // If the data has not changed, no recordset is returned
 113
 114 $updateSQL = $conn->GetUpdateSQL($rs, $record);
 115 $conn->Execute($updateSQL,$varr); // Update the record in the database
 116 if ($conn->Affected_Rows() != 1)print "<p><b>Error1 </b>: Rows Affected=".$conn->Affected_Rows().", should be 1</p>";
 117
 118 $record["firstName"] = "Caroline".rand();
 119 $record["lasTname"] = "Smithy Jones"; // Update Caroline's lastname from Miranda to Smith
 120 $record["creAted"] = '2002-12-'.(rand()%30+1);
 121 $record['num'] = 331;
 122 $updateSQL = $conn->GetUpdateSQL($rs, $record);
 123 $conn->Execute($updateSQL,$varr); // Update the record in the database
 124 if ($conn->Affected_Rows() != 1)print "<p><b>Error 2</b>: Rows Affected=".$conn->Affected_Rows().", should be 1</p>";
 125
 126 $rs = $conn->Execute("select * from ADOXYZ where lastname like 'Sm%'");
 127 //adodb_pr($rs);
 128 rs2html($rs);
 129
 130 $record["firstName"] = "Carol-new-".rand();
 131 $record["lasTname"] = "Smithy"; // Update Caroline's lastname from Miranda to Smith
 132 $record["creAted"] = '2002-12-'.(rand()%30+1);
 133 $record['num'] = 331;
 134
 135 $conn->AutoExecute('ADOXYZ',$record,'UPDATE', "lastname like 'Sm%'");
 136 $rs = $conn->Execute("select * from ADOXYZ where lastname like 'Sm%'");
 137 //adodb_pr($rs);
 138 rs2html($rs);
 139 }
 140
 141
 142 testsql();
 143 ?>