Force MySQL to return something versus nothing?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

User avatar
JAB Creations
DevNet Resident
Posts: 2341
Joined: Thu Jan 13, 2005 6:44 pm
Location: Sarasota Florida
Contact:

Force MySQL to return something versus nothing?

Post by JAB Creations »

Let's say I execute the following MySQL query...

Code: Select all

SELECT tag_idFROM blog_tagsWHERE tag_name = 'exists1'OR tag_name = '!exist'OR tag_name = 'exists2'
I'm looking to compare the array with another however can I somehow force MySQL to return something (such as a null of sorts) versus a missing row altogether? If not then at least in my current mindset I'd have to SELECT the tag_id row for loop most likely which makes less sense then if I can minimize the number of queries overall.

Thoughts please?
User avatar
JAB Creations
DevNet Resident
Posts: 2341
Joined: Thu Jan 13, 2005 6:44 pm
Location: Sarasota Florida
Contact:

Re: Force MySQL to return something versus nothing?

Post by JAB Creations »

In MySQL is there an equivalent to PHP's echo? If so then I could simply use an if null argument and then "echo" null instead of not having a row returned.
User avatar
jayshields
DevNet Resident
Posts: 1912
Joined: Mon Aug 22, 2005 12:11 pm
Location: Leeds/Manchester, England

Re: Force MySQL to return something versus nothing?

Post by jayshields »

I don't understand why you'd need to do this.

If you're desperate just do

Code: Select all

if(mysql_num_rows($result) == 0) echo "it's empty";
User avatar
JAB Creations
DevNet Resident
Posts: 2341
Joined: Thu Jan 13, 2005 6:44 pm
Location: Sarasota Florida
Contact:

Re: Force MySQL to return something versus nothing?

Post by JAB Creations »

No...

I will be assigning values from the MySQL array to another array. However if a row is missing (and there will almost always be a missing row) then the data won't line up and the whole array code will be in vain.

The main point of doing this is to avoid making multiple MySQL queries...there absolutely has to be a way to return a row with something...anything even if one does not exist from the database. Something *has* to be returned...allowing one query total...otherwise I'll end up having to put a query in to a loop and executing as many times as the loop executes! I'd really like to avoid that and learn a new trick at the same time if at all possible. Plus this will help me get more comfortable with arrays which has started to become a personal campaign of mine.

Also I'm not desperate for a reply though one helpful in even giving me an idea of something to query on Google would be greatly appreciated. I work indy for myself with the code so I don't have someone over my shoulder counting a clock of doom or anything of the sorts. :mrgreen:
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Force MySQL to return something versus nothing?

Post by VladSun »

Code?
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
JAB Creations
DevNet Resident
Posts: 2341
Joined: Thu Jan 13, 2005 6:44 pm
Location: Sarasota Florida
Contact:

Re: Force MySQL to return something versus nothing?

Post by JAB Creations »

My initial post for this thread contains all the code for the query I have right now.

I've been searching through a few things such as trying to do an if else in MySQL however I don't know how to "echo" in MySQL in example (or if it's even possible).

What is in the database doesn't matter...all that matters is that if my single query looks for 26 values it returns 26 rows...45 values it returns 45 rows. If 25 or 44 rows values aren't found then I want it to still return 26 or 45 rows.

I've been Googling random guesses for the past four hours so I'd appreciate even a hint at what I search for online even.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Force MySQL to return something versus nothing?

Post by VladSun »

JAB Creations wrote:My initial post for this thread contains all the code for the query I have right now.
JAB Creations wrote:I will be assigning values from the MySQL array to another array. However if a row is missing (and there will almost always be a missing row) then the data won't line up and the whole array code will be in vain.

The main point of doing this is to avoid making multiple MySQL queries...there absolutely has to be a way to return a row with something...anything even if one does not exist from the database. Something *has* to be returned...allowing one query total...otherwise I'll end up having to put a query in to a loop and executing as many times as the loop executes! I'd really like to avoid that and learn a new trick at the same time if at all possible. Plus this will help me get more comfortable with arrays which has started to become a personal campaign of mine.
....
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Force MySQL to return something versus nothing?

Post by Eran »

Maybe if you explained better what is your original need and included some code, people would have ideas on better ways to implement what you are after
User avatar
JAB Creations
DevNet Resident
Posts: 2341
Joined: Thu Jan 13, 2005 6:44 pm
Location: Sarasota Florida
Contact:

Re: Force MySQL to return something versus nothing?

Post by JAB Creations »

UltimateGoal: Edit tags for an existing blog post using minimal number of MySQL queries.

Secondary Goal: Must determine if tag exists in tag table (not the relational tag table).

Third Goal: If the tag exists in the tag table add it to relational table else if the tag does not exist in the tag table it must be added to the tag table before it can be added to the relational table.

So...

I'm trying to build an array of id's for the tags from the tag table first which is what this thread is all about.

Once I have that array then I can compare the arrays and where tags don't yet exist in the tag table build up a single MySQL query to INSERT all the new tags.

Then once all those new tags exist take get the ID's once more and INSERT to the tag relational table.

It's a bit of a mess right now because unfortunately there is both a lot of code the inability to coherently minimize the explanation. That is why I am trying to be as specific as I can for this thread.

As far as this thread is concerned I'm trying to align arrays however if I remove a tag from an existing blog post then if that is the fifth of twenty tags all tags five and greater will not align as desired and the wrong tags will be added or removed. Therefor by having a null value when a row is otherwise not returned whatsoever I retain that structure. Otherwise at least from my POV I'll end up having to stick a MySQL query inside of a loop and that just screams noob to me. :mrgreen:

I have successfully created the opposite where I have to remove tags when they no longer appear in the $_POST array for editing a blog entry.

Here is the PHP and MySQL example where I have successfully created a *single* dynamic delete query to remove all the relational tag rows for a blog entry if they are removed from a blog entry...

Code: Select all

<?php
include("_0_header_02_mysql.php");
$thread_tags_new = $_POST['tags_new'];
$thread_tags_old = $_POST['tags_old'];
$tags_array_new = explode(', ',$thread_tags_new);
$tags_array_old = explode(', ',$thread_tags_old);
 
$tags_remove = array_diff($tags_array_old, $tags_array_new);
$result = mysql_query("SELECT tag_id FROM blog_tags WHERE tag_name='".implode("' OR tag_name='",$tags_remove)."'");
//$row = mysql_fetch_array($result);
 
 
$count = count($tags_remove);
echo '<div>count == '.$count.'</div>';
 
 
 
 
echo '<div>';
$i = 0;
while ($i <= $count && $row = mysql_fetch_array($result)) {
    //print_r($row[0]);
    echo '<div>'.$i.' == '; print_r($row[0]); echo '</div>';
    $i++;
    $new_array[$i] = $row[0];
}
 
mysql_free_result($result);
echo '</div>';
 
//print_r($new_array);
//$query2 = "DELETE FROM blog_xhref_tags WHERE xhref_thread_id='1' AND (xhref_tag_id='".implode("' OR xhref_tag_id='",$tags_remove)."')";
 
echo "DELETE FROM blog_xhref_tags WHERE xhref_thread_id='1' AND (xhref_tag_id='";
print_r(implode("' OR xhref_tag_id='",$new_array));
echo "')";
?>
<form action="<?php
if ($_GET['tags'] == "1") {echo 'test5.php?tags=2';}
else if ($_GET['tags'] == "2" || !isset($_GET['tags'])) {echo 'test5.php?tags=1';}
?>" method="post">
<fieldset>
<div><label for="tags_new">Tags</label><input id="tags_new" name="tags_new" style="width: 512px;" value="<?php
$tags1 = 'application/xhtml+xml, XML, XHTML, CSS, Flash, ASP.NET, Java';
$tags2 = 'application/xhtml+xml, XML, XHTML, CSS, JavaScript, PHP, MySQL';
if ($_GET['tags'] == "1") {echo $tags1;}
else if ($_GET['tags'] == "2" || !isset($_GET['tags'])) {echo $tags2;}
?>" /></div>
 
<input name="tags_old" type="hidden" value="<?php
$tags1 = 'application/xhtml+xml, XML, XHTML, CSS, Flash, ASP.NET, Java';
$tags2 = 'application/xhtml+xml, XML, XHTML, CSS, JavaScript, PHP, MySQL';
if ($_GET['tags'] == "2") {echo $tags1;}
else if ($_GET['tags'] == "1" || !isset($_GET['tags'])) {echo $tags2;}
?>" />
 
<div><input type="submit" /></div>
</fieldset>
</form>

Code: Select all

---- Table structure for table `blog_tags`-- CREATE TABLE IF NOT EXISTS `blog_tags` (  `tag_id` INT(6) NOT NULL AUTO_INCREMENT,  `tag_name` VARCHAR(64) COLLATE utf8_unicode_ci NOT NULL,  `tag_name_base` VARCHAR(64) COLLATE utf8_unicode_ci NOT NULL,  PRIMARY KEY  (`tag_id`)) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=39 ; ---- Dumping data for table `blog_tags`-- INSERT INTO `blog_tags` (`tag_id`, `tag_name`, `tag_name_base`) VALUES(1, 'application/xhtml+xml', 'applicationxhtmlxml'),(2, 'XML', 'xml'),(3, 'XHTML', 'xhtml'),(4, 'CSS', 'css'),(5, 'JavaScript', 'javascript'),(6, 'Example Tag', 'example_tag'),(7, 'Tag1', 'tag1'),(8, 'Tag2', 'tag2'),(9, 'Tag3', 'tag3'),(19, 'MySQL', 'mysql'),(18, 'PHP', 'php'),(36, 'Flash', 'flash'),(37, 'ASP.NET', 'aspnet'),(38, 'Java', 'java'); SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";-- Database: `jabcreat_members` -- Table structure for table `blog_xhref_tags` CREATE TABLE IF NOT EXISTS `blog_xhref_tags` (  `xhref_id` INT(6) NOT NULL AUTO_INCREMENT,  `xhref_tag_id` INT(6) NOT NULL,  `xhref_thread_id` INT(6) NOT NULL,  PRIMARY KEY  (`xhref_id`)) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=31 ; -- Dumping data for table `blog_xhref_tags` INSERT INTO `blog_xhref_tags` (`xhref_id`, `xhref_tag_id`, `xhref_thread_id`) VALUES(1, 1, 1),(2, 2, 1),(3, 3, 1),(4, 4, 1),(5, 2, 2),(6, 3, 3),(7, 4, 4),(8, 3, 2),(9, 2, 3),(10, 1, 4),(11, 3, 4),(12, 3, 6),(13, 3, 5),(14, 3, 7),(28, 34, 1),(27, 33, 1),(29, 5, 1);
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Force MySQL to return something versus nothing?

Post by Eran »

The way I would do it, is first figure out which tags have been added to the post, check if those exist in the tag table and if not add them. After which I can edit the post and add the proper relations to the tags:

Code: Select all

 
$thread_tags_new = $_POST['tags_new'];
$thread_tags_old = $_POST['tags_old'];
$tags_array_new = explode(', ',$thread_tags_new);
$tags_array_old = explode(', ',$thread_tags_old);
$tags_remove = array_diff($tags_array_old, $tags_array_new);
 
$tags_add = array_diff($tags_array_new,$tags_array_old); //By switching the order of the arrays, we diff the opposite way
if(count($tags_add) > 0 ) { //If there are tags to add, check if they exist in the tag table
 
    $query = "SELECT tag_name FROM blog_tags WHERE tag_name='".implode("' OR tag_name='",$tags_add). "'";
    $result = mysql_query($query);
    $existing_tags = array();
    while($row = mysql_fetch_assoc($result) ) {
        $existing_tags[] = $row['tag_name'];
    }
    $tags_add_tagtable = array_diff($tags_add,$existing_tags); //Filter to the tags that don't exist yet
    
    foreach($tags_add_tagtable as $newtag) { //Iterate over non-existing tags and add to tag table
        $query = "INSERT INTO blog_tags (tag_name) VALUES ('" . mysql_real_escape_string($newtag) . "')";
        mysql_query($query);
    }  
}
 
After the tags are created you can continue with your previous logic. The code I suggested is not complete obviously, you have to modify it to your application exact needs.
User avatar
JAB Creations
DevNet Resident
Posts: 2341
Joined: Thu Jan 13, 2005 6:44 pm
Location: Sarasota Florida
Contact:

Re: Force MySQL to return something versus nothing?

Post by JAB Creations »

Thanks for the reply pytrin, I'm taking a look at it now.

In the mean time a good question just popped in to my head: does MySQL actually return null for empty/non-existing rows though PHP *drops* such rows? If so then how could I prevent it from doing so? If not well...I'm looking and soon messing with the code posted, thanks!
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Force MySQL to return something versus nothing?

Post by Eran »

If you want to retrieve all the rows in a table and replace values for specific columns depending on some condition, then that can be done. Either you are returning all the rows or are filtering based on a certain criteria - there is no such thing as non-existing rows (how should MySQL know how many such rows to return?)
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Force MySQL to return something versus nothing?

Post by VladSun »

Add appropriate constraints (unique) to both table and use only REPLACE queries. If a record you are trying to insert already exists then its insertion will be skipped. No SELECTs for checking existance needed ;)

So ... it will be 2 queries!
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Force MySQL to return something versus nothing?

Post by Eran »

But that will be an INSERT query per tag. By filtering beforehand, you possibly avoid any INSERT queries at all.

Not that it makes that much of a difference - this takes place in the administration, where presumably less users interact.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Force MySQL to return something versus nothing?

Post by VladSun »

Yes, indeed there are issues with my solution, but let Jab tries it ;)
There are 10 types of people in this world, those who understand binary and those who don't
Post Reply