Page 1 of 2

Force MySQL to return something versus nothing?

Posted: Sun Nov 23, 2008 2:16 pm
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?

Re: Force MySQL to return something versus nothing?

Posted: Sun Nov 23, 2008 3:26 pm
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.

Re: Force MySQL to return something versus nothing?

Posted: Sun Nov 23, 2008 4:12 pm
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";

Re: Force MySQL to return something versus nothing?

Posted: Sun Nov 23, 2008 4:35 pm
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:

Re: Force MySQL to return something versus nothing?

Posted: Sun Nov 23, 2008 4:52 pm
by VladSun
Code?

Re: Force MySQL to return something versus nothing?

Posted: Sun Nov 23, 2008 4:56 pm
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.

Re: Force MySQL to return something versus nothing?

Posted: Sun Nov 23, 2008 5:00 pm
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.
....

Re: Force MySQL to return something versus nothing?

Posted: Sun Nov 23, 2008 5:23 pm
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

Re: Force MySQL to return something versus nothing?

Posted: Sun Nov 23, 2008 5:57 pm
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);

Re: Force MySQL to return something versus nothing?

Posted: Sun Nov 23, 2008 8:04 pm
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.

Re: Force MySQL to return something versus nothing?

Posted: Sun Nov 23, 2008 8:30 pm
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!

Re: Force MySQL to return something versus nothing?

Posted: Sun Nov 23, 2008 9:55 pm
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?)

Re: Force MySQL to return something versus nothing?

Posted: Mon Nov 24, 2008 3:51 am
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!

Re: Force MySQL to return something versus nothing?

Posted: Mon Nov 24, 2008 3:58 am
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.

Re: Force MySQL to return something versus nothing?

Posted: Mon Nov 24, 2008 4:04 am
by VladSun
Yes, indeed there are issues with my solution, but let Jab tries it ;)