Mysql Stored Procedure Error

[skaterdav85]

Im trying to create a SP in phpmyadmin. In the SQL box i typed this directly from a book I'm reading, and I get an error. What does this mean?

CREATE PROCEDURE getCustInf()BEGINSELECT * FROM customers;END;

ErrorSQL query: CREATE PROCEDURE getCustInf( ) BEGIN SELECT * FROM customers;MySQL said: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 3

[justsomeguy]

I don't think you can create SPs from phpMyAdmin, I think it will only run one statement at a time and stops after the first semicolon. The SQL query it printed out doesn't contain the 'END;'.

[skaterdav85]

so how do you go about creating and using SP's in MySQL if it doesnt work in phpmyadmin?

[justsomeguy]

You would need to use the MySQL command line console instead of phpMyAdmin. The PHP interface to MySQL only allows one query at a time, so any time you're using MySQL through PHP you'll be limited to one query. The command line is a direct interface to the database.

[skaterdav85]

ok thanks. is it common to use SP's in MySQL? justsomeguy, do you use mostly MySQL SP's in your php development?

[justsomeguy]

Not personally, most of what I'm doing isn't complex enough to warrant an SP, but I've used them occasionally.

[skaterdav85]

do you hardcode your queries into your script, or do you just write the query as a variable in another file, and then include that file whenever you do that same query?...just curious, do you use phpmyadmin or the command line more? ... ive noticed im doing alot of the same queries over and over again, so im curious as to how you do yours since you're an expert!

[justsomeguy]

I wouldn't really call myself an expert, but most of my queries are in the script. I use a database class to handle that aspect of it, and I just send the query and the parameters to the class and it returns the results. For most changes to the database structure I'll just use phpMyAdmin.

[GerryH]

I've never tried it myself, but they mention using the DELIMITER to keep mySql from stopping at the first semi-colon.http://www.mysql.com/news-and-events/newsl...0000000297.html

[skaterdav85]

I wouldn't really call myself an expert, but most of my queries are in the script. I use a database class to handle that aspect of it, and I just send the query and the parameters to the class and it returns the results. For most changes to the database structure I'll just use phpMyAdmin.

Do you fetch & display the results within the class or do you just return the database resource to your object and then display your results outside the class in your script?

I've never tried it myself, but they mention using the DELIMITER to keep mySql from stopping at the first semi-colon.http://www.mysql.com/news-and-events/newsl...0000000297.html

ya i tried that...didn't work. it's a bummer it doesnt work like that. i hate using the command line.

[justsomeguy]

Do you fetch & display the results within the class or do you just return the database resource to your object and then display your results outside the class in your script?

The class returns an array of rows instead of a resource object.

[skaterdav85]

The class returns an array of rows instead of a resource object.

I'm new to OOP. Does your class for querying your MySQL db look something like this? Any advice?

class MySQLdb {	private $_host;	private $_username;	private $_pw;	private $_query;	private $_db;	public function __construct($host, $username, $password, $db, $query) {		$this->_host = $host;		$this->_username = $username;		$this->_pw = $password;		$this->_query = $query;		$this->_db = $db;	}	public function mysqlFunc() {		$dbc = mysqli_connect($this->_host, $this->_username, $this->_pw);		$database = mysql_select_db($this->_db);		$query1 = mysql_query($this->_query);		return $query1;	}}$queryMySQL = new MySQLdb('localhost','root','','test','Select * from CustomerInfo');$queryResult = $queryMySQL->mysqlFunc();while($row = mysql_fetch_array($queryResult)) {	echo "ID: $row[id] | Name: $row[name]";	echo '<br/>';}

[justsomeguy]

Sort of, it looks more like this:

<?phpclass tc_lms_database{  private $host = '';  private $user = '';  private $pass = '';  private $db = '';  private $conn = false;  public $sql_stmt = '';  public $params = false;  private $result = false;  private $rowset = false;  private $last_stmt = '';  /*  Constructor    Set the host, username, password, and database, then connect  */  function __construct($h = '', $u = '', $p = '', $d = '')  {    if ($h != '')    {      $this->host = $h;      $this->user = $u;      $this->pass = $p;      $this->db = $d;      $this->connect();    }  }  /*  connect    Connect to the database using the supplied information, trigger an error if there was a problem  */  function connect($h = '', $u = '', $p = '', $d = '')  {    if ($h != '')    {      $this->host = $h;      $this->user = $u;      $this->pass = $p;      $this->db = $d;    }    $this->conn = mysql_connect($this->host, $this->user, $this->pass) or trigger_error(mysql_error(), E_USER_ERROR);    if ($this->db != '')      mysql_select_db($this->db, $this->conn) or trigger_error($this->error(), E_USER_ERROR);  }  /*  reset    Reset the SQL statement and all parameters  */  function reset()  {    $this->sql_stmt = '';    $this->params = false;  }  /*  sql    Set the SQL statement  */  function sql($str)  {    $this->sql_stmt = $str;  }  /*  add_param    Add a parameter for the SQL statement, and optionally escape it.  Parameters should be added  in the same order that they appear in the SQL  */  function add_param($val, $escape = true)  {    if ($escape)      $val = "'" . $this->escape($val) . "'";    if (!$this->params)      $this->params = array();    $this->params[] = $val;  }  /*  exec  Execute a SQL statement and return the result.  If the argument is true, do not die on an error.  This returns a mysql result resource.  */  function exec($passthrough_errors = false)  {    $this->check_db();    if ($this->sql_stmt == '')      trigger_error('SQL statement not set', E_USER_ERROR);    if (is_array($this->params) && count($this->params))    {      $this->sql_stmt = vsprintf($this->sql_stmt, $this->params);      $this->params = false;    }    $this->last_stmt = $this->sql_stmt;    if ($passthrough_errors)      return mysql_query($this->sql_stmt);    else      return mysql_query($this->sql_stmt) or trigger_error($this->error(), E_USER_ERROR);  }  /*  get_row  Get a single row from the given result set  */  function get_row($result)  {    return mysql_fetch_assoc($result);  }  /*  select  Execute a SELECT statement, with an optional limit and page number.  This returns the  results as an array of rows, not as a mysql result resource.  e.g.:  $db->select(10, 3); // get the 10 items on page 3  */  function select($limit = 0, $page = 0)  {    $this->check_db();    if ($this->sql_stmt == '')      trigger_error ('SQL statement not set', E_USER_ERROR);    if (is_array($this->params) && count($this->params))    {      $this->sql_stmt = vsprintf($this->sql_stmt, $this->params);      $this->params = false;    }    if ($limit)    {      $this->sql_stmt .= ' LIMIT ';      if ($page)        $this->sql_stmt .= (($page - 1) * $limit) . ',';      $this->sql_stmt .= $limit;    }    $this->last_stmt = $this->sql_stmt;    $this->result = mysql_query($this->sql_stmt) or trigger_error($this->error(), E_USER_ERROR);    $this->rowset = array();    while ($row = mysql_fetch_assoc($this->result))    {      // convert null to empty string      foreach ($row as $k => $v)      {        if (is_null($v))          $row[$k] = '';      }      $this->rowset[] = $row;    }    mysql_free_result($this->result);    return $this->rowset;  }  /*  delete    Execute a DELETE statement with an optional limit  */  function delete($limit = 0)  {    $this->check_db();    if ($this->sql_stmt == '')      trigger_error('SQL statement not set', E_USER_ERROR);    if (is_array($this->params) && count($this->params))    {      $this->sql_stmt = vsprintf($this->sql_stmt, $this->params);      $this->params = false;    }    if ($limit)      $this->sql_stmt .= " LIMIT {$limit}";    $this->last_stmt = $this->sql_stmt;    return mysql_query($this->sql_stmt) or trigger_error($this->error(), E_USER_ERROR);  }  /*  insert  Insert a record into the given table.  The $params variable should be an associative  array of column names and values.  e.g.;  $db->insert('table_name', array('id' => 10, 'username' => 'Joe'));  */  function insert($table, $params)  {    $this->check_db();    if (!is_array($params) || count($params) == 0)      return false;    $names = '';    $values = '';    foreach ($params as $k => $v)    {      $names .= "{$k},";      $values .= "'" . $this->escape($v) . "',";    }    $names = substr($names, 0, -1);    $values = substr($values, 0, -1);    $this->last_stmt = "INSERT INTO {$table} ({$names}) VALUES ({$values})";    return mysql_query("INSERT INTO {$table} ({$names}) VALUES ({$values})", $this->conn) or trigger_error($this->error(), E_USER_ERROR);  }  /*  update  Update a record in the given table.  Similar to insert, but the third parameter is the expression  to use in the WHERE clause.  e.g.:  $db->update('table_name', array('username' => 'John'), 'id=10');  */  function update($table, $params, $where = '')  {    $this->check_db();    if (!is_array($params) || count($params) == 0)      return false;    $cols = '';    foreach ($params as $k => $v)      $cols .= "{$k}='" . $this->escape($v) . "',";    $cols = substr($cols, 0, -1);    if ($where != '')      $where = " WHERE {$where}";    $sql = "UPDATE {$table} SET {$cols} {$where}";    $this->last_stmt = $sql;    return mysql_query($sql, $this->conn) or trigger_error($this->error(), E_USER_ERROR);  }  /*  escape    Escape a string for use in a query  */  function escape($str)  {    /* if (get_magic_quotes_gpc())      $str = stripslashes($str); */    return mysql_real_escape_string($str, $this->conn);  }  /*  strip    Remove selected characters from a string  */  function strip($str)  {    $replace = array(      "'",      '"',      '-',      ';'    );    return str_replace($replace, '', $str);  }  /*  insert_id    Get the last autoassigned ID  */  function insert_id()  {    return mysql_insert_id($this->conn);  }  /*  num_rows    Get the number of rows produced by the given SQL statement  */  function num_rows($sql)  {    return mysql_num_rows(mysql_query($sql, $this->conn));  }  /*  check_db    Make sure the database has been connected to  */  function check_db()  {    if ($this->conn == false)      die('Database not initialized');  }  /*  error    Generate the last error message  */  function error()  {    global $page_mode, $sess;    if (empty($page_mode))      $page_mode = '';    return 'Page mode: ' . $page_mode . '; User ID: ' . $sess->userid . '; Database Error #' . mysql_errno($this->conn) . ': ' . mysql_error($this->conn) . '.  SQL Query: ' . $this->last_stmt;  }  /*  get_last_sql    Get the last SQL statement executed, after parameter replacement  */  function get_last_sql()  {    return $this->last_stmt;  }}?>

I create an object like this:require_once 'class.db.php';$db = new tc_lms_database($config['db_host'], $config['db_user'], $config['db_pass'], $config['db_name']);This is my user registration script, it uses several other classes but has examples of selects and inserts:

<?php# used on login page and admin add user# !$sess->any_admin implies self-registrationif (!$sess->any_admin && !$opts->get_opt('user_register')){  $response['errors']['reason'] = 'User registrations have been disabled.  Only administrators are allowed to register new users.';  return;}if ($sess->ug_admin && !$opts->get_opt('ugadmin_addusers')){  $response['errors']['reason'] = 'User Group Admins do not have permission for that action.';  return;}$email_req = !$opts->get_opt('email_optional');$insert = array();$insert['username'] = form_var('username');$insert['email'] = form_var('email');$insert['fname'] = uc_names(form_var('fname'));$insert['lname'] = uc_names(form_var('lname'));$password = form_var('password');$conf_password = form_var('conf_password');$user_type = intval(form_var('user_type', USER_TYPE_STUDENT));$usergroup = intval(form_var('usergroup'));$lic_key = form_var('lic_key');if (!$sess->any_admin && $opts->get_opt('licensing') && $lic_key == ''){  $response['errors']['reason'] = 'The license key was not found.  A valid license key is required.';  return;}# validate license keyif (!$sess->any_admin && $opts->get_opt('licensing')){  $lic_key = strtolower(str_replace('-', '', $lic_key));  $db->sql('SELECT id, start_date, exp_days, users FROM usergroups WHERE lic_key=%s');  $db->add_param($lic_key);  $ug = $db->select();  if (!$ug)  {	$response['errors']['reason'] = 'The license key was not valid.';	return;  }  $ug = $ug[0];  if ($ug['start_date'] == 0)  {	$response['errors']['reason'] = 'That license key does not have a valid start date, the administrator must define an effective date for the license key.';	return;  }  # validate the date  if ($ug['exp_days'] > 0)  {	$now = time();	if ($now < $ug['start_date'])	{	  $response['errors']['reason'] = 'That license key is not valid yet, the effective date has not been reached.  That license key is scheduled to become active on ' . date($config['datestamp'], $ug['start_date']) . '.';	  return;	}	if ($now > $ug['start_date'] + ($ug['exp_days'] * 86400))	{	  $response['errors']['reason'] = 'The license key has expired and is no longer valid.';	  return;	}  }  # validate number of users  if ($ug['users'] > 0)  {	$uids = get_usergroup_users($ug['id']);	if (count($uids) > 0)	{	  $db->sql('SELECT COUNT(id) AS num FROM users WHERE id IN (' . implode(',', $uids) . ') AND ug_admin=0 AND sub_admin=0 AND main_admin=0');	  $num = $db->select();	  if ($num[0]['num'] >= $ug['users'])	  {		$response['errors']['reason'] = 'The maximum number of allowed users for that license key has been reached.  No other users may register unless a user is removed or the user limit for that key is increased.';		return;	  }	}  }    # add the user to the usergroup  $usergroup = $ug['id'];}$insert['instructor'] = 0;$insert['ug_admin'] = 0;$insert['sub_admin'] = 0;$insert['main_admin'] = 0;if ($sess->any_admin){  // only admins can change user type  switch ($user_type)  {	case USER_TYPE_STUDENT:	break;	case USER_TYPE_INSTRUCTOR:	  $insert['instructor'] = 1;	break;	case USER_TYPE_UGADMIN:	  $insert['ug_admin'] = 1;	break;	case USER_TYPE_SUBADMIN:	  if ($sess->main_admin || $sess->sub_admin)		$insert['sub_admin'] = 1;	break;	case USER_TYPE_MAINADMIN:	  if ($sess->main_admin)		$insert['main_admin'] = 1;	break;  }}if ($db->num_rows("SELECT id FROM users WHERE username='" . $db->escape($insert['username']) . "'")){  $response['errors'][] = array('id' => 'reg_username', 'msg' => 'That username belongs to another user.');  return;}if ($insert['email'] != '' && $db->num_rows("SELECT id FROM users WHERE email='" . $db->escape($insert['email']) . "'")){  $response['errors'][] = array('id' => 'email', 'msg' => 'That email address belongs to another user.');  return;}if (($insert['email'] != '' || $email_req) && !validate_email($insert['email'])){  $response['errors'][] = array('id' => 'email', 'msg' => 'That email address was not recognized as a valid format.');  return;}if (strlen($insert['username']) < 3){  $response['errors'][] = array('id' => 'reg_username', 'msg' => 'The username needs to be at least 3 characters long.');  return;}if (strlen($password) < 4){  $response['errors'][] = array('id' => 'reg_password', 'msg' => 'The password needs to be at least 4 characters long.');  return;}if ($password != $conf_password){  $response['errors'][] = array('id' => 'conf_password', 'msg' => 'The confirmation password did not match.');  return;}$uf_list = array();$ufields = $uf->get_fields();for ($i = 0; $i < count($ufields); $i++){  if ($ufields[$i]->shown)  {	$uf_list[] = array('id' => $ufields[$i]->id, 'val' => form_var($ufields[$i]->id));	$insert[$ufields[$i]->id] = form_var($ufields[$i]->id);  }}$insert['password'] = sha1($password);$insert['active'] = 1;$insert['date_registered'] = time();$db->insert('users', $insert);$uid = $db->insert_id();if ($user_type == USER_TYPE_STUDENT || $user_type == USER_TYPE_INSTRUCTOR){  if ($usergroup != 0)	$db->insert('usergroup_users', array('ugid' => $usergroup, 'uid' => $uid));  // process autoassignments - new way  $db->sql('SELECT id, autoassign FROM usergroups');  $ugs = $db->select();  foreach ($ugs as $ug)  {	if ($ug['autoassign'] != '')	{	  # run autoassignments for each UG to check if this user fits	  $db->sql('SELECT id FROM users WHERE id=%d AND (' . $ug['autoassign'] . ')');	  $db->add_param($uid, false);	  $res = $db->select();	  if (count($res))		$db->insert('autoassigned_users', array('ugid' => $ug['id'], 'uid' => $uid));	}  }  rebuild_user_content($uid);}elseif ($user_type == USER_TYPE_UGADMIN && $usergroup != 0){  $db->insert('usergroup_admins', array('ugid' => $usergroup, 'uid' => $uid));}$response['success'] = true;?>

For a select query you send a SQL statement using the sprintf syntax, and then add each parameter. When you add a parameter you can tell it whether or not to automatically escape and quote the parameter, so strings get escaped and quoted while numbers don't. To insert I just send it an array with the values, it will escape everything for me. Updating works the same way, here's the script for a user to update their information:

<?php$update = array();$update['email'] = form_var('email');$update['fname'] = uc_names(form_var('fname'));$update['lname'] = uc_names(form_var('lname'));$password = form_var('password');$conf_password = form_var('conf_password');$email_req = !$opts->get_opt('email_optional');if (!$opts->get_opt('lock_standard_fields')){  if ($update['email'] != '' && $db->num_rows("SELECT id FROM users WHERE email='" . $db->escape($update['email']) . "' AND id!={$sess->userid}"))  {	$response['errors'][] = array('id' => 'email', 'msg' => 'That email address belongs to another user.');	return;  }  if (($update['email'] != '' || $email_req) && !validate_email($update['email']))  {	$response['errors'][] = array('id' => 'email', 'msg' => 'That email address was not recognized as a valid format.');	return;  }}else{  // don't update standard fields if they're locked  unset($update['email']);  unset($update['fname']);  unset($update['lname']);}if ($password != ""){  if (strlen($password) < 4)  {	$response['errors'][] = array('id' => 'password', 'msg' => 'The password needs to be at least 4 characters long.');	return;  }  if ($password != $conf_password)  {	$response['errors'][] = array('id' => 'conf_password', 'msg' => 'The confirmation password did not match.');	return;  }  if (sha1($password) == $sess->userdata['password'])  {	$response['errors'][] = array('id' => 'password', 'msg' => 'You cannot use the same password you currently have.');	return;  }}$uf_list = array();$ufields = $uf->get_fields();for ($i = 0; $i < count($ufields); $i++){  // dont update non-editable fields unless the user is an admin  if ($ufields[$i]->shown && ($sess->any_admin || $ufields[$i]->editable))  {	$uf_list[] = array('id' => $ufields[$i]->id, 'val' => form_var($ufields[$i]->id));	$update[$ufields[$i]->id] = form_var($ufields[$i]->id);  }}if ($password != ""){  $update['password'] = sha1($password);  $update['pw_last_change'] = time();}$db->update('users', $update, "id={$sess->userid}");$sess->update($update);if (!$sess->main_admin && !$sess->ug_admin && !$sess->sub_admin){  // run autoassignments  $db->sql('DELETE FROM autoassigned_users WHERE uid=%d');  $db->add_param($sess->userid, false);  $db->delete();    $db->sql('SELECT id, autoassign FROM usergroups');  $ugs = $db->select();  foreach ($ugs as $ug)  {	if ($ug['autoassign'] != '')	{	  # run autoassignments for each UG to check if this user fits	  $db->sql('SELECT id FROM users WHERE id=%d AND (' . $ug['autoassign'] . ')');	  $db->add_param($sess->userid, false);	  $res = $db->select();	  if (count($res))		$db->insert('autoassigned_users', array('ugid' => $ug['id'], 'uid' => $sess->userid));	}  }    rebuild_user_content($sess->userid);}$update['fullname'] = $sess->fullname;$update['fname'] = $sess->userdata['fname'];$update['lname'] = $sess->userdata['lname'];$update['email'] = $sess->userdata['email'];$response['update'] = $update;$response['success'] = true;?>

Deletes work more like selects, here's the script to delete a user:

<?phpif (!$sess->any_admin){  $response['errors']['reason'] = 'You do not have permission.  Your log-in session might have expired.';  return;}if ($id == $sess->userid){  $response['errors']['reason'] = 'You cannot delete your own account.';  return;}if ($sess->ug_admin && !$opts->get_opt('ugadmin_editusers')){  $response['errors']['reason'] = 'User Group Admins do not have permission for that action.';  return;}if ($sess->ug_admin){  $allowed = get_ugadmin_uids($sess->userid);  if (!in_array($id, $allowed))  {	$response['errors']['reason'] = 'You do not have permission to delete that user, the user is not part of your user group.';	return;  }}// check for instructor classes$db->sql('SELECT COUNT(id) AS num FROM standup_instance WHERE instructor=%d');$db->add_param($id, false);$check = $db->select();if ($check[0]['num'] > 0){  $response['errors']['reason'] = 'This user is marked as the instructor for ' . $check[0]['num'] . ' classes.  You cannot delete an instructor assigned to classes.';  return;}$db->sql('INSERT INTO archive_users SELECT * FROM users WHERE id=%d');$db->add_param($id, false);$db->exec();$db->sql('INSERT INTO archive_session SELECT * FROM content_session WHERE uid=%d');$db->add_param($id, false);$db->exec();$db->sql('INSERT INTO archive_session_history SELECT * FROM content_session_history WHERE uid=%d');$db->add_param($id, false);$db->exec();$db->sql('DELETE FROM users WHERE id=%d');$db->add_param($id, false);$db->delete();$db->sql('DELETE FROM content_session WHERE uid=%d');$db->add_param($id, false);$db->delete();$db->sql('DELETE FROM content_session_history WHERE uid=%d');$db->add_param($id, false);$db->delete();$db->sql('DELETE FROM assignment_cache WHERE uid=%d');$db->add_param($id, false);$db->delete();$db->sql('DELETE FROM autoassigned_users WHERE uid=%d');$db->add_param($id, false);$db->delete();$db->sql('DELETE FROM calendar_events WHERE apply_type=\'user\' AND apply_to=%d');$db->add_param($id, false);$db->delete();$db->sql('DELETE FROM instructor_eval WHERE uid=%d OR instructor=%d');$db->add_param($id, false);$db->add_param($id, false);$db->delete();$db->sql('DELETE FROM instructor_eval_answers WHERE eid NOT IN (SELECT id FROM instructor_eval)');$db->delete();$db->sql('DELETE FROM learning_track_progress WHERE uid=%d');$db->add_param($id, false);$db->delete();$db->sql('DELETE FROM standup_roster WHERE uid=%d');$db->add_param($id, false);$db->delete();$db->sql('DELETE FROM test_answers WHERE uid=%d');$db->add_param($id, false);$db->delete();$db->sql('DELETE FROM user_content WHERE uid=%d');$db->add_param($id, false);$db->delete();$db->sql('DELETE FROM user_content_groups WHERE uid=%d');$db->add_param($id, false);$db->delete();$db->sql('DELETE FROM usergroup_admins WHERE uid=%d');$db->add_param($id, false);$db->delete();$db->sql('DELETE FROM usergroup_users WHERE uid=%d');$db->add_param($id, false);$db->delete();$response['success'] = true;?>

The class also has the generic exec function to just execute the query and return the result, whatever it is.