PHP - How To Check If Database Query Is Null?

Ok, this may be just because I have been programming all day and my mind has gone blank (happens alot), but this is my PHP script:

Code: [Select]
<?php
$query_distinct_item_types = mysql_query("SELECT DISTINCT name FROM item_types");
while($item_types = mysql_fetch_array($query_distinct_item_types)){
$distinct_item_types[] = $item_types['name'];
}

foreach($distinct_item_types as $item){
$query_item_total = mysql_query("SELECT item_type, SUM(price) WHERE item_type='$item' FROM costs GROUP BY item_type");
while($item_total = mysql_fetch_array($query_total_price)){
$item_totals[] = $item_total['SUM(price)'];
}
}

$item_summery = $item_totals;
?>

$item_summery which is = to $item_totals is returning null, any idea's?

I am facing problem to execute query by assigning NULL value to a variable and then executing query.In MySQL DB four fields Mobile,landline, pincode,dob are set as integer and date(for dob) respectively.The default is set as  NULL and NULL option is selected as yes.All these fields are not mandatory.The problem is that when I edit the form my keeping the value as empty in DB these are saved as 0, 0 , 0 & 0000-00-00 inspite of Null. I have tried everything but still the defect persist. Please help me to come out of the problem 

The code, I have used:

<?php
//require_once 'includes/config.php';

      $dbusername = $_POST['email'];
        $dbfirstname = $_POST['first_name'];
        $dblastname = $_POST['last_name'];
        //$dbmobile_number = $_POST['mobile'];
      if (isset($_POST['mobile']))
      
      {
         $dbmobile_number = $_POST['mobile'];
         
      }
       
      else
      
      {
         $dbmobile_number = "NULL";
         
      }
            
      $dblandline_number = $_POST['landline'];
        $dbdob = $_POST['dob'];
      
      if(isset($_POST['is_email']))
      {
         $dbSubscribe_Email_Alert = '1';
      }
      else
      {
         $dbSubscribe_Email_Alert = '0';
      }
      
      if(isset($_POST['is_sms']))
      {
         $dbSubscribe_SMS = 0;
      }
      else
      {
         $dbSubscribe_SMS = 0;
      }
       
      
        $dbAddress_firstname = $_POST['shipping_first_name'];
        $dbAddress_lastname = $_POST['shipping_last_name'];
        $dbAddress = $_POST['shipping_address'];
        $dbcity = $_POST['shipping_city'];
        $dbpincode = $_POST['shipping_pincode'];
        $dbstate = $_POST['shipping_state'];
        $dbcountry = $_POST['shipping_country'];
      
      echo "Welcome".$dbusername;

//if($_POST['btnSave'])

      //if ($_POST['btnSave'])

//{
   
   //echo "Inside query loop";
$connect = mysql_connect("localhost","root","") or die("Couldn't connect!");
mysql_select_db("salebees") or die ("Couldn't find DB");

//$query = mysql_query("SELECT * FROM users WHERE username='$username'");

$query = mysql_query("update users set firstname = '$dbfirstname', lastname = '$dblastname', mobile_number = '$dbmobile_number', landline_number = '$dblandline_number', dob = '$dbdob', Subscribe_Email_Alert = '$dbSubscribe_Email_Alert', Subscribe_SMS = '$dbSubscribe_SMS',  Address_firstname = '$dbAddress_firstname', Address_lastname = '$dbAddress_lastname', Address = '$dbAddress', city = '$dbcity', pincode = '$dbpincode', state = '$dbstate', country = '$dbcountry'      where username = '$dbusername' ");

header("location:my_account.php");


//}

//else

//{
//die();

//}


?>

How can I check to see if a query has failed, be it INSERT, UPDATE, DELETE or SELECT?

I want to return one value if the query fails, but a different one if it works.

For example, if I have a query to delete a row, but somehow this query fails because the row doesn't exist, then I want to check that it's failed and then output a result

Hi.  I have wrote a simple login script.  Works perfectly.  Now as my users can only register one email address at the minute if you register two the same you get the default sql error pop up.  I want my own error so at the beginning of my script i wrote the following code. however.  It does not seem to be running.  It does not create an error but still allows duplicate emails in the database.  can anyone see the error of my ways?
<?php session_start();
if(isset($_POST['create'])){    
 //script for checking if the email account already exists
$qCheckEmail = "SELECT * FROM members WHERE email='".$_POST['email']."'";
$rCheckEmail = mysql_query($qCheckEmail);
$Email = mysql_fetch_array($rCheckEmail);
 
 if($_POST['email'] == $Email['email']) 
 {$emailDup = "This email account has already been registered";}
 else
 { 
//Process data for validation    
$forename =  trim($_POST['forename']);    
$surname =  trim($_POST['surname']);    
$email      =  trim($_POST['email']);        
$password   =  trim($_POST['password']);     
$gender     =  trim($_POST['gender']);
$birthday =  $_POST['birthday'];
$creationdate = $_POST['creationdate'];
$validationID = $_POST['validationID'];
$accesslevel =  trim($_POST['accesslevel']);
$accountType =  trim($_POST['accountType']);
// then the rest of the login script here

hello
I want query from one table and insert in another table on another domain .
each database on one domain name.
for example http://www.site.com $con1
and http://www.site1.com $con.

can anyone help me?
my code is :
<?php



$dbuser1 = "insert in this database";
$dbpass1 = "insert in this database";
$dbhost1 = "localhost";
$dbname1 = "insert in this database";

// Connecting, selecting database

$con1 = mysql_connect($dbhost1, $dbuser1, $dbpass1)
   or die('Could not connect: ' . mysql_error());



mysql_select_db($dbname1) or die('Could not select database');


$dbuser = "query from this database";
$dbpass = "query from this database";
$dbhost = "localhost";
$dbname = "query from this database";

// Connecting, selecting database

$con = mysql_connect($dbhost, $dbuser, $dbpass)
   or die('Could not connect: ' . mysql_error());

mysql_select_db($dbname) or die('Could not select database');

//query from database
$query = mysql_query("SELECT * FROM `second_content` WHERE CHANGED =0 limit 0,1");
    while($row=mysql_fetch_array($query)){
$result=$row[0];
$text=$row[1]."</br>Size:(".$row[4].")";
$alias=$row[2];
$link = '<a target="_blank" href='.$row[3].'>Download</a>';
echo $result;
}  
//insert into  database
mysql_query("SET NAMES 'utf8'", $con1); 
$query3= " INSERT INTO `jos_content`
 (`id`,
  `title`,
   `alias`,
    `) VALUES 
                        (NULL, 
                        '".$result."', 
                        '".$alias."', 
                       '')";
if (!mysql_query($query3,$con1))
  {
  die('Error: text add' . mysql_error());
  }


            mysql_close($con);
                        mysql_close($con1);
             


?>

Hey Guys,
I need some assistance, hopefully there is just a simple command that I am not aware of or an easy answer to this.

Suppose I have an array with x amount of values...
$arr = array("111111" => 10, "222222" => 20);
And i have a query..
mysql_query("SELECT * FROM table WHERE table.zip = "111111" ");

Suppose I want to modify the query, so it can check if table.zip exists anywhere in $arr, regardless of length of array. And Select the value that the array location refers to. So I need it to check if "111111" is anywhere in the array, and then take the value 10.

The example I am applying this to is that I have a database full of business addresses/zip codes and a database full of every zip code in country. So I have a script to get every zip within x miles, and the distance from it, but need to be able to apply it when I query my business addresses to see if it exists within x miles.

Thanks!
Greg

I am fairly new to PHP and want to someone to check my reasoning and logic for a php query.  I have a mySQL table that includes a field called authors.  This field has 1+ authors names in it.  If there is more than one author in the field, it has been concatenated in the following format: lastname1, firstinitial1., lastname2, first initial2., & lastname3, firstinitial3

Let's say I have three fields:
lastname1, first initial1.
lastname1, firstinitial1., lastname2, first initial2., & lastname3, firstinitial3.
lastname3, firstinitial3.


If I do a php count query on the author field I will get 1 count for the multiple field, 1 count for the lastname1 author, 1 count for the lastname3 author and 0 for the lastname2 author even though lastname1 author should be counted twice, correct? Is there anyway to write a query that will count each specific instance in the field as a hit?  Am I logically tracking or off the bubble?  Thanks!

Hello there,

I'm new to this site/forum so i dont know if this is the right forum to post a code review / commentary request ....

I have a function that handels the sql code...
Know i would like to know what you think off it ? can I do something different or better ?..
Code: [Select]
<?PHP
/*
* Private function db_query($sql) | handle....
* whit checking en extendid error reporting....
* Runs a query but does not return a result array....
* @String $sql | this is the sql query you whant to run.....
*/
private function db_query($sql) {
$this->sql = $sql;
switch ( DEBUG_QUERY ) { // check debug mode...

case true:
try { // probeer query uit te voeren...

$handle = $this->query($this->sql);

if(!$handle) // if error whit the query...
{
$this->rollback();
throw new Exception('MySQLi Query went wrong error ==> ' . mysqli::$error);
}

} catch (Exception $e) { // error afhandeling and reporting..
echo '<hr />';
echo '<span style="color:red"><b>A MySQLi Query went wrong:</b></span><br />';
echo var_dump($e->getMessage());
echo '<br />';
echo nl2br($e->getTraceAsString());
echo '<br />';
echo 'Error in File: ' . $e->getFile();
echo '<br />';
echo 'Thrown Exception on line: ' . $e->getLine();
echo '<br /><hr />';
exit();
} // end error afhandeling and reporting...
break;

case false:
$handle = $this->query($this->sql);
break;
} // end switch...
return $handle;
}



/*
* Public function db_Do | handels the insert, update, select and delete query's
* A lot off optional options for the different query's
* @String $type | Choose between the four type's | select, insert, update or delete | Default is Select
* @String $table | Select witch table you whant to use | give a vailid tablename you whant to use in the query
* @String $values | Input the values uw whant to select from the table | * for all - row1, row2, row3 | id, username, password
* @String $where | The where operator for the query | Where $where = | give a vailid row name | if used you must fill in the other two where operators | default = empty (optional)
* @String $opparator | The operator for the where operator | =, >, <, <>, >=, <=, !=, ==, ===, !==, LIKE, IS, IS NOT, +, -, /, %, * | whit check in_array | where 2 | default = empty (optional)
* @String / Int $where_val | The where value for the where operator | WHERE $where{id} $opparator{=} $where_val{1} | where 3 | default = empty (optional)
* @Bool $use_and | if TRUE you can use the AND operator | linked to the other three and operators | if you use 1 you must fill in all 4 of them | default = false (optional)
* @String $and_key | Value for the AND operator | same as $order_by | AND $and_key{username} | and 2 | default = empty (optional)
* @String $and_oparetor | The operator for the and section | same as $opparator | =, >, <, <>, >=, <=, !=, ==, ===, !==, LIKE, IS, IS NOT, +, -, /, %, * | whit check in_array | and 3 | default = '=' (optional)
* @String $and_value | The value for by the and_key | same as $where_val | AND $and_key{username} $and_oparetor{=} $and_value{'jhon'} | and 4 | default = empty (optional)
* @String $order_by | value for the Order by operator only used if hase a value | ORDER BY $order_by{id} | if used you must alsow fill in the second order by operator | default = empty (optional)
* @String $order_key | Value for the Order key by the order value | ORDER BY $order_by{id} $order_key{asc, desc} | check in_array | default = asc (optional)
* Error msg and checks includid, Failsafe...
* Runs the query and returns a row....
* Uses the db_query function...
* Version 1.0.0
*/
public function db_Do($type = 'select', $table, $values, $where = NULL, $opparator = NULL, $where_val = NULL, $use_and = FALSE, $and_key = NULL, $and_oparetor = '=', $and_value = NULL, $order_by = NULL, $order_key = 'ASC') {

switch ( DEBUG_QUERY ) { // check debug mode...

case true:
if(in_array($type, $this->SQL_TYPE, TRUE)) { // check for correct $type...
if(in_array($order_key, $this->ORDER_KEY, TRUE)) { // check if order key is allowd $order_key....
if(in_array($opparator, $this->OPARATORS, TRUE)) { // check for vallid oparetors...
if(empty($table) or strlen($table) >= 4) { // check if $table correct is....
if(empty($values) or strlen($values) >= 4) { // check if the $values are given correctly....
if(in_array($and_oparetor, $this->OPARATORS, TRUE)) { // check if and oparetor is allowd.....

switch( $type ) { // witch type...

case 'select':
// Build the SQL Query....
$query = 'SELECT '. $this->real_escape_string($values) .' FROM '. $table .' ';
if(!empty($where) and (empty($where_val) or empty($opparator))) {
$row = 'Sorry you have to fill in all 3 of the where conditions!'; return $row; }
elseif(!empty($where) || !empty($where_val) || !empty($opparator)) {
$query .= 'WHERE '. $where .' '. $opparator .' "'. $this->real_escape_string($where_val) .'" '; }
if($use_and == true and !empty($and_key) and !empty($and_value)) {
$query .= 'AND '. $and_key .' '. $and_oparetor .' "'. $this->real_escape_string($and_value) .'" '; }
elseif($use_and == true and (empty($and_key) or empty($and_value))) {
$row = 'Sorry you have to fill in all 3 off the AND oparetors correctly.'; return $row; }
if(!empty($order_by)) {
$query .= ' ORDER BY '. $order_by .' '. $order_key .''; }

$this->sql = $query;
$handle = $this->db_query($this->sql);
$row = $handle->fetch_assoc();
mysqli_free_result($handle);
break;

case 'insert':
// Build the SQL Query......
$query = 'INSERT INTO '. $table .' ('. $this->real_escape_string($values) .') ';
$query .= 'VALUES ('. $this->real_escape_string($where) .')';

$this->sql = $query;
$handle = $this->db_query($this->sql);
$row = ($handle) ? true : false;
unset($handle); // empty / unset $handle...
break;

case 'update':
// Build the SQL Query......
$query = 'UPDATE '. $table .' ';
$query .= 'SET '. $this->real_escape_string($values) .' ';

if(!empty($where) and !empty($where_val) and !empty($opparator)) {
$query .= 'WHERE '. $where .' '. $opparator .' "'. $this->real_escape_string($where_val) .'" '; }
elseif(empty($where) or empty($where_val) or empty($opparator)) {
$row = 'Sorry you have to fill in all 3 of the where conditions!'; return $row; }
if($use_and == true and !empty($and_key) and !empty($and_value)) {
$query .= 'AND '. $and_key .' '. $and_oparetor .' "'. $this->real_escape_string($and_value) .'" '; }
elseif($use_and == true and (empty($and_key) or empty($and_value))) {
$row = 'Sorry you have to fill in all 3 off the AND oparetors correctly.'; return $row; }

$this->sql = $query;

$handle = $this->db_query($this->sql);
$row = ($handle) ? true : false;
unset($handle); // empty / unset $handle....
break;

case 'delete':
//Construct the delete query.....
$query = 'DELETE FROM '. $table .' ';
$query .= 'WHERE '. $where .' '. $opparator .' "'. $this->real_escape_string($where_val) .'" ';
if($use_and == true and !empty($and_key) and !empty($and_value)) {
$query .= 'AND '. $and_key .' '. $and_oparetor .' "'. $this->real_escape_string($and_value) .'" '; }
elseif($use_and == true and (empty($and_key) or empty($and_value))) {
$row = 'Sorry you have to fill in all 3 off the AND oparetors correctly.'; return $row; }

$this->sql = $query;

$handle = $this->db_query($this->sql);
$row = ($handle) ? true : false;
unset($handle); // empty / unset $handle....
break;

} // end switch( $type ).....


} else { // Correct Oparetors......
$row = 'Incorrect Oparetor in the AND section choose out: =, >, <, <>, >=, <=, !=, ==, ===, !==, LIKE, IS, IS NOT, +, -, /, %, * or use the FreeQuery';
}
} else { // Correct VALUES.....
$row = 'Sorry you have to fill in the values parameter correctly and it hase to be bigger then 3 chars.';
}
} else { // Correct TABLE....
$row = 'Sorry you have to fill in the table parameter correctly and it hase to be bigger than 3 chars.';
}
} else { // Correct Oparetors......
$row = 'Incorrect Oparetor in the WHERE section choose out: =, >, <, <>, >=, <=, !=, ==, ===, !==, LIKE, IS, IS NOT, +, -, /, %, * or use the FreeQuery';
}
} else { // if order_key is NOT allowd....
$row = 'Incorrect Order by opparator: <b>'. $order_key .'</b> choos between (asc or desc)';
}
} else { // if not correct type return error msg....
$row = 'Incorrect type: <b>'. $type . '</b> choose between (select, insert, update or delete)';
}
break; // end case true...

case false:


break; // end case false...

} // end switch( debug_query )...
return $row;

} // end public function db_Do().....

?>
It's still a work in process so it's not done yet...

I'm working on a directory component and have kind of a tricky thing I need to do.

The list of results pop through ajax on the left side along with it's google map number tag, and the google map pops on the right side. To make it even trickier the results are popped through a radius search by proximity.

The problem is if there are multiple results with the same address you end up having all of the number tags on the map buried under the last tag with that address, plus it makes for kind of a long list.

What I need to do is run a check to see if the previous result is the same address, and if yes not pop that result in a new table cell, rather stick it in a jquery drop down div so that all of the following results with the same address are stuffed in the jquery drop down in the same table cell under the first result with that address.

In a perfect world all of the listings with the same addresses would be submitted in order so I could just do a check for the previous itemid's address, but of course that's not the case. I thought about running a query to grab ALL of the addresses and then check the item's address against it to see if it matches and if yes echo the jquery div instead of the <td>, but obviously that won't work because that will cause the first item with that address to be put in the div also. Plus I don't know if that's a very efficient way to go about it anyway.

Here's kind of the basic thing I'm working with:
Code: [Select]
$query = "SELECT data_txt FROM jos_sobi2_fields_data WHERE fieldid = 76 AND itemid = $mySobi->id";
$result = mysql_query($query) or die(mysql_error());
while($row = mysql_fetch_array($result)){
$myaddress = $row['data_txt'];
}
echo "<td $style>";

Any ideas?

Sorry going to be a big post, but trying to query a sql database for a value

Original code

<?php if($this->item->params->get('catItemExtraFields') && count($this->item->extra_fields)): ?>
     <!-- Item extra fields -->
     <div class="catItemExtraFields">
        <h4><?php echo JText::_('K2_ADDITIONAL_INFO'); ?></h4>
        <ul>
         <?php foreach ($this->item->extra_fields as $key=>$extraField): ?>
         <?php if($extraField->value): ?>
         <li class="<?php echo ($key%2) ? "odd" : "even"; ?> type<?php echo ucfirst($extraField->type); ?> group<?php echo $extraField->group; ?>">
            <span class="catItemExtraFieldsLabel"><?php echo $extraField->name; ?></span>
            <span class="catItemExtraFieldsValue"><?php echo $extraField->value; ?></span>
         </li>
         <?php endif; ?>
         <?php endforeach; ?>
         </ul>
       <div class="clr"></div>
     </div>
     <?php endif; ?>


Now I want to check the name field for a value and if it is Closing Date print out something different from the value in value, so I've changed the red code to

<span class="catItemExtraFieldsLabel"><?php if $extraField->name ="Closing date"; ?></span>
            {
            <span class="catItemExtraFieldsLabel"><?php echo $extraField->name; ?></span>
             <span class="catItemExtraFieldsValue"><?php echo $this->item->publish_down, JText::_('K2_DATE_FORMAT_LC2')); ?></span>
                }
                else
                {
                <span class="catItemExtraFieldsLabel"><?php echo $extraField->name; ?></span>
            <span class="catItemExtraFieldsValue"><?php echo $extraField->value; ?></span>
                }
            <?php endif; ?>

Doesn't work though, tried a heap of variations but can't figure it out.

Thanks

Marc

Hey All, first off Merry X-Mas

ok so I'm trying to insert something into the batabse in one of three areas' let me explain

there are three fields in the users database bounceone, bouncetwo and bouncethree now what I want to do is when the user clicks add to bounce list insert a value into the databse in one of these three hole, if bounceone is empty put it there, if not check bouncetwo if it's empty put it there, if not check the final one and if it's empty put it there else echo sorry please either remove a bounce

any ideas on how this can be done.

Hi guys,

I need your help. I am checking on a database as I want to see if I have the same value in the url and in the database.


Code: [Select]
<?php
session_start();
    define('DB_HOST', 'localhost');
    define('DB_USER', 'mydbuser');
    define('DB_PASSWORD', 'mydbpass');
    define('DB_DATABASE', 'mydbtable');
       
    $errmsg_arr = array();
    $errflag = false;

    $link = mysql_connect(DB_HOST, DB_USER, DB_PASSWORD);
    if(!$link) {
  die('Failed to connect to server: ' . mysql_error());
    }

    $db = mysql_select_db(DB_DATABASE);
    if(!$db) {
  die("Unable to select database");
    }

   function clean($var){

return mysql_real_escape_string(strip_tags($var));
    }
  
    $username = clean($_GET['user']);
    $password = clean($_GET['pass']);
    $test = clean($_GET['test']);
    $public = clean($_GET['public']);
   
   
   if (isset($_GET['user']) && (isset($_GET['pass']))) {
    if($username == '' || $password == '') {
  $errmsg_arr[] = 'username or password are missing';
  $errflag = true;
    }
} elseif (isset($_GET['user']) || (isset($_GET['test'])) || (isset($_GET['public']))) {
    if($username == '' || $test == '' || $public == '') {
  $errmsg_arr[] = 'user or others are missing';
  $errflag = true;
    }
  }

    if($errflag) {
  $_SESSION['ERRMSG_ARR'] = $errmsg_arr;
  echo implode('<br />',$errmsg_arr);
   }
   else {
  $qry="SELECT * FROM members WHERE username='$username' AND passwd='$password'";
  $result=mysql_query($qry) or die('Error:<br />' . $qry . '<br />' . mysql_error());


if ($username && $password) {
  if(mysql_num_rows($result) > 0) {
     $qrytable1="SELECT images, id, test, links, Public FROM user_list WHERE username='$username'";
    $result1=mysql_query($qrytable1) or die('Error:<br />' . $qry . '<br />' . mysql_error());

    while ($row = mysql_fetch_array($result1)) {
        echo "<p id='test'>";
        echo $row['test'] . "</p>";
        echo '<p id="images"> <a href="images.php?test=test&id='.$row['id'].'">Images</a></td> | <a href="http://' .  $row["links"] . '">Link</a> </td> | <a href="delete.php?test=test&id='.$row['id'].'">Delete</a> </td> | <span id="test">'.$row['Public'].'</td>';
     }
   } else {
    echo "user not found";
  }
} elseif($username && $test && $public) {
    $qry="SELECT * FROM members WHERE username='$username'";
    $result1=mysql_query($qry) or die('Error:<br />' . $qry . '<br />' . mysql_error());
    
    if(mysql_num_rows($result1) > 0) {     
      $qrytable1="SELECT Public FROM user_list WHERE username='$username' && test='$test'";
      $result2=mysql_query($qrytable1) or die('Error:<br />' . $qry . '<br />' . mysql_error());
      
      if(mysql_num_rows($result2) > 0) {
        $row = mysql_fetch_row($result2);
        mysql_query("UPDATE user_list SET Public=('$_GET[public]') WHERE username='$username' AND test='$test'");
        echo "update!";
      } else {
        echo "already updated!";
      }
    } else {
      echo "user not found";
    }
  }
} 
?>
This is the function I use to check the value in the database:

Code: [Select]
if (mysql_affected_rows($result2) > 0) {
         mysql_query("UPDATE user_list SET Public=('$_GET[public]') WHERE username='$username' AND test='$test'");
         echo "you have update it!";
       } else if (mysql_affected_rows($result2) < 0) {
         echo "it is not on the database";
       } else {
         echo "you have already updated!";
       }

When i input the different value in a url bar while the records are not the same as the value in the url and in the database, i can't get passed and I am keep getting "you have already updated!!" when the value in a database are different than I have input in a url.

Do you know how i can get pass it when I have input the different value in the url while it is not the same in the database?

Any advice would be much appreicated.

Thanks,
Mark

Hello
i have a text box and a button on my webpage and want to get the text box to find the username i type into it from the database and when it does it will then go to number and delete any text that is under usernames number field as you can see i have no idea how.

Code: [Select]
$result = mysql_query("DELETE FROM mytable WHERE username='$username1' AND number='$number1'" );
Code: [Select]
<form name="form1" method="post" action="p.php">
<p><input type="submit" value="Enter" /></p>
<input name="username" type="text" id="username" size="15" maxlength="35" value="<?php echo $username1; ?>" />
</form>

How do you check if a MySQL or MySQLi server is available through php.
I'm trying to make a short script that makes sure the users server can run the application. Like all the popular ones do

hi,