rcorlew

left joins only returns relevant data(only table.rows) that are not null right joins return all data, whether it is null or not. If you want to return rows that have null rows, you should try using right join

Yeah, I was just giving some advice that they could expand on based on the information that was posted. I was trying to show how to relate two or more tables based on a common tie that would make sense to someone tackling a new problem.

Try changing your code to this and see what happens: $today = date('Y-m-d H:i:s'); $expire = date('Y-m-d' '23:59:59');

I would use a schema like this: Software - ref id = 1 (in it's own table as Categories) Computer Games - ref id = 100 Strategy - ref id = 1000 Hardware - ref id = 2 (in it's own table as Categories) Mainboard - ref id = 200 Asus - ref id =2000 The reason a schema like this is important: <ol> <li>Makes maintaining your database easier</li> <li>You can trim off 0's or add 0's to get up or down the list</li> <li>The ref id cross referencing would make much more sense</li> <li>This schema utilizes good database normalization methods</li> <li>You would only have to do a simple "join" to link the two tables together using a coder friendly db design</li> </ol> I think you can get the picture with that.

Currently I am using mysql_real_escape_string help stop sql injection, but I am running a boolean search that explodes the input into an array. Each space separated item/word is an item in the array, and then it is queried with the following code: <?php $query = "SELECT id, title, location, page_text, MATCH(title,page_text) AGAINST('+$expanded[0] ~$expanded[1] ~$expanded[2] ~$expanded[3]' IN BOOLEAN MODE) AS score FROM pages WHERE MATCH (title,page_text) AGAINST ('+$expanded[0] ~$expanded[1] ~$expanded[2] ~$expanded[3]' IN BOOLEAN MODE) ORDER BY score DESC"; ?> My question is this, isn't the boolean search itself removed from the threat of sql inection with out having to escape anything?

The file extension depends on the type of server you are using. It was just an idea I had after reading about how Yahoo and Google use a similar scheme to help hide the script type they are using. Thanks for the feedback.

The 7 pages are every page minus the forum and user page. I think that number might end up being much higher as the site grows.

I think that this site is pretty much done now, take a look and let me know what you all think. I have changed the address a little bit to make the site more unique, a small bit of programming nirvana that took a week to iron all the bugs out of. So here is the new link, the old one will still work, but you will end up here anyway. http://www.mycrdisorder.org/index.crd I think the file extension conversion is pretty cool myself if nobody else notices.

I like it, nice and to the point. I agree the nav buttons are a bit too big, but hey it's makes for some easy clickin' right.

I would think about changing the background color of the navbar on the left, maybe more of a grayish type of green. I do think that the site is nicely laid out and easy to navigate and read, good job on that.

Ok, I think that everything is ready for another round of testing. I have fixed the cussing filter, and don't allow password as a password 'again', but I can't find any more odd behaviors for now. If anyone finds any, please let me know.

your code would be <?php require("../../charts.php"); ?> ../ will move you down one level towards the root

That is fixed now. One thing I have been trying to figure out is why if I search for Chron's which is the database, it will not return results on my web server, but on the test server it does. I know it is because of the way they escape certain characters, but I am at a loss. I think I will email them and see how they have that set to be escaped.

Check into database normalization, as mysql has many date, time, mathematical functions it supports natively to make your coding life much easier. Nothing like saving keystrokes to make life easier.

The reason str_ireplace throws a wobbly is because that function is only available in versions > 5. I have a custom function that replaces str_ireplace for versions < 5 if you would like.

I just fixed all but the posting of blank forum articles. The host I use just did some updates and messed up a few of my things, I did not even notice some of them, but what a headache to change all those pages because your host changes something. I should have the cuss word filter reinstalled, I sure hate web hosts somethimes. They say that they support php5, but the native support is php4, and some of the read/write restrictions are not set up correctly for my site to run in php5. So I had to write some custom functions to make some functions that are in version 5 but not in version 4. Oh well, all should be fixed shortly as most is already done.

You should use a 'Common' key (userid) or something to link the two tables together. Then you would use an join (left join or inner join) to match the two tables up.

Change this: $query = "INSERT INTO $dbtable Into this: $query = "INSERT INTO '$dbtable' You need to tell mysql that you are using a variable in the query string.

$query = mysql_query("SELECT * FROM table_name WHERE satus = '$my_status'");

I have created a nice little search engine for my site that is almost complete. It has all the cool things like highlighting, boolean and so on. When you click on a link with results, it will then highlight your search on the next page. I am just curious as to what others may think of it, and if it works as they would expect it to. http://www.mycrdisorder.org/search.php You may notice that the links will show up with a .crd instead of .php , just something I thought would add a little character to my site.

It is MyIsam, I don't know what the default char set is, my hosting company has those privileges tied down.

I have an issue with the character set returned by my search results page. I am using mysql, and am getting back some odd results. I keep getting ’ instead of ' . Is there a way to change this?

Ok I finally got this done, since I have not seen or read of anything that will truly accomplish any sort of true scoring, I will post my code so all the others who may be looking for the answer for this problem will have at least a good starting point. If you would like further clarification, message me and I will help you out since this seems to be a situation that most people tend to give up on. Here is the full script, I have taken time to ensure that this works, first of all you have to have the table set up as MyIsam, took me a while to realize that I have it as InnoDB, which is not FULLTEXT search able. <?php echo "<div align='center'>"; echo"<form action='$PHP_SELF?function=search' method='POST'> Search for : <input type='text' name='s_word' size='20' value='$cs_word'> <input type='submit' name='submit' value='Search' /> </form></div><br />"; $searchit = "$cs_word"; $expanded = preg_split("/[\s,]+/", $cs_word); $nx = count($expanded); $query = "SELECT id, title, location, page_text, MATCH(title,page_text) AGAINST('+$expanded[0] ~$expanded[1] ~$expanded[2] ~$expanded[3]' IN BOOLEAN MODE) AS score FROM pages WHERE MATCH (title,page_text) AGAINST ('+$expanded[0] +$expanded[1] +$expanded[2] +$expanded[3]' IN BOOLEAN MODE) ORDER BY score DESC"; $result = mysql_query($query); $count = mysql_num_rows($result); $result2 = mysql_query("SELECT * FROM pages"); $page_count = mysql_num_rows($result2); $result3 = mysql_query("SELECT * FROM pages WHERE title LIKE '%$cs_word%' OR page_text LIKE '%$cs_word%'"); if($count < 2) $rc = "result"; if($count > 1) $rc = "results"; echo "<div id='searchHeader'>We found $count $rc on $page_count pages</div>"; echo "<br />"; if($count == 0) { echo "<b>Sorry, no results found.</b> <br />"; } if($count > 0) { while ($row = mysql_fetch_assoc($result)) { $string = "$row[page_text]"; $pos = stripos("$string", "$expanded[0]"); $ncount = substr_count($string, $expanded[0]); $i = 1; foreach($expanded as $nval) { $pos1 = strripos("$string", "$nval"); if($pos1 != null) { $i++; } } if($ncount < 2) { $tc = "match"; } if($ncount > 1) { $tc = "matches"; } $ls = $pos - 60; if ($pos > 60) { $lsr = $ls; } else { $lsr = 0; } $purl = "$row[location]"; $replacement = "<span id='sText'>$expanded[0]</span>"; $replacement2 = "<span id='sText'>$expanded[1]</span>"; $searchout = substr($string, $lsr, 200); $search_results = eregi_replace($expanded[0], $replacement, $searchout); $p = ceil($nx / $i); if(($i - 1) == $nx) { $p = 1; } $my_score = ceil(100 / $p); $l_id = $ncount + $my_score; $r_output[$ncount] = " <b>Score $my_score%</b><br /> <span id='sResult'> <a href='$purl$ltype'>$row[title]</a> </span> - <span id = 'myForm'>$ncount $tc on this page</span><br /> <div id='sShow'> $search_results </div> <div id='sLink'>http://www.mycrdisorder.org/$purl$ltype</div> <br />"; } krsort($r_output); //Now we will print out the sorted results by true relevance foreach($r_output as $val) { echo "$val"; } } ?>

Ok, here is the search that I have now, and as yet to have developed a way to weigh the total number of matches that are produced by each search word. The query returns results just fine, but I would like to know if there is a way to ORDER by SUM(matches('$expanded[0], page_text)) like you can do with php itself. Here is the query: <?php $query = "SELECT id, title, location, page_text, MATCH(title,page_text) AGAINST('+(>$expanded[0]) +(>$expanded[1]) +(>$expanded[2]) +(>$expanded[3])' IN BOOLEAN MODE) AS score FROM pages WHERE MATCH (title,page_text) AGAINST ('+$expanded[0] +$expanded[1] +$expanded[2] +$expanded[3]' IN BOOLEAN MODE) > 0 HAVING page_text LIKE '%$cs_word%' ORDER BY score DESC"; ?> I have not seen a way to do this yet, if there is please let me know.

I have a search script that I have been working on for a while. It works very well, sort of like google, displays the correct text from the DB that it has matched, highlights the search phrase just fine. I am however having trouble getting it to order it's results by the number of matches it has made from the DB. My script searches through a table that holds the title, link and the entire text for every page on my site. It is not too big since I currently only have 10 or so pages, but that number will end up at around 100 or so. My table is like this: id = self explanatory title = page title link = page location p_text = the text from the page I am having no luck sorting the results by relevance, although I can see the relevance. I don't know if this is possible with sql or not. Here is the basis of my code: <?php $result = mysql_query("SELECT * FROM pages WHERE page_text LIKE '%$cs_word%'"); $count = mysql_num_rows($result); if($count == 0) { echo "<b>Sorry, no results found.</b>"; } if($count > 0) { while ($row = mysql_fetch_array ($result)) { $string = "$row[page_text]"; $searchit = "$cs_word"; $pos = stripos("$string", "$searchit"); $ncount = substr_count($string, $searchit); $ls = $pos - 60; echo "$ncount"; if ($pos > 60) { $lsr = $ls; } else { $lsr = 0; } $purl = "$row[location]"; $replacement = "<span id='sText'>$searchit</span>"; $searchout = substr($string, $lsr, 200); $search_results = eregi_replace($searchit, $replacement, $searchout); echo "<div id='sResult'>"; echo "<a href='$purl$ltype'>$row[title]</a>"; echo "</div>"; echo "<div id='sShow'>"; echo "$search_results"; echo "</div>"; echo "<div id='sLink'>http://www.mycrdisorder.org/$purl$ltype</div>"; $s_count = str_word_count($search_results); echo "<br />"; } }echo "See results in the <a href='forum$ltype?function=search&searchterm=$cs_word'>forum</a>"; ?> Like I said, the script is great except for the sorting by total matches in the text.