djneel
-
Posts
22 -
Joined
-
Last visited
Never
Posts posted by djneel
-
-
Hi all, quick question:
I am trying to do build a simple search function for my website. Simply said, the script below gives you a view on what I'm trying to do.
SELECT * FROM table WHERE title, contents, author LIKE '%keyword%' ORDER BY date DESC
So I tried this:
'SELECT * FROM table WHERE title LIKE "%'.$keyword.'%" OR contents LIKE "%'.$keyword.'%" OR author LIKE "%'.$keyword.'%" ORDER BY date DESC';
The script above only produces limited results (most of the time, I only get results when the title matches...). When I replace OR with AND, I get no results...
Anyhow, my question is: do I have to make several queries, one for each row? Or is there a way to lookup data in every row with just one query?
My MySQL version is: 'MySQL - 5.0.27-standard'
Thanks!
-
Please help, anyone!
I have to get the memberId of the person with the highest, latest testresult.
So memberId 4 would not be it now, since memberId 4 has not performed as well on his latest test.
Please, please, please!
Maybe this will be more useful:
[code]-- phpMyAdmin SQL Dump
-- version 2.9.0-rc1
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generatie Tijd: 20 Jan 2007 om 13:32
-- Server versie: 5.0.24
-- PHP Versie: 5.1.6
--
-- Database: `wecompete`
--
-- --------------------------------------------------------
--
-- Tabel structuur voor tabel `test`
--
CREATE TABLE `test` (
`id` int(255) NOT NULL auto_increment,
`date` datetime NOT NULL,
`gymId` int(255) NOT NULL,
`memberId` int(255) NOT NULL,
`trainerId` int(255) NOT NULL,
`isCardio` tinyint(1) NOT NULL,
`cardioDistance` int(255) default NULL,
`cardioDuration` int(255) default NULL,
`cardioInclination` int(255) default NULL,
`isPower` tinyint(1) NOT NULL,
`powerReps` int(255) default NULL,
`powerWeight` int(255) default NULL,
`isConstitution` tinyint(1) NOT NULL,
`constitutionFat` int(255) default NULL,
`constitutionHeight` int(255) default NULL,
`constitutionWeight` int(255) default NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=23 ;
--
-- Gegevens worden uitgevoerd voor tabel `test`
--
INSERT INTO `test` (`id`, `date`, `gymId`, `memberId`, `trainerId`, `isCardio`, `cardioDistance`, `cardioDuration`, `cardioInclination`, `isPower`, `powerReps`, `powerWeight`, `isConstitution`, `constitutionFat`, `constitutionHeight`, `constitutionWeight`) VALUES
(1, '2006-12-03 16:05:13', 1, 1, 1, 1, 3500, 15, 6, 1, 12, 100, 1, 7, 184, 74),
(2, '2006-12-19 21:11:15', 2, 2, 10, 1, 2500, 15, 8, 1, 6, 80, 1, 20, 160, 43),
(3, '2006-12-17 21:12:29', 3, 3, 8, 1, 2000, 15, 2, 0, 0, 0, 1, 36, 170, 84),
(4, '2006-12-13 21:13:51', 4, 4, 3, 0, 0, 0, 0, 1, 50000, 50000, 1, 16, 170, 74),
(5, '2006-12-29 11:12:56', 1, 3, 1, 1, 2500, 15, 3, 1, 16, 75, 1, 8, 184, 76),
(8, '2006-12-29 11:12:19', 1, 1, 1, 1, 1500, 15, 15, 1, 13, 75, 1, 9, 184, 76),
(9, '2006-12-31 03:12:31', 3, 2, 4, 1, 5000, 15, 3, 0, 0, 0, 0, 0, 0, 0),
(10, '2006-12-31 04:12:01', 2, 2, 9, 1, 3000, 15, 2, 1, 12, 60, 1, 14, 158, 43),
(11, '2007-01-06 04:01:01', 4, 2, 3, 1, 2500, 15, 3, 1, 12, 50, 0, 0, 0, 0),
(21, '2007-01-19 07:01:03', 2, 4, 2, 0, 0, 15, 0, 1, 4, 4, 0, 0, 0, 0),
(20, '2007-01-19 01:01:17', 1, 4, 5, 0, 0, 15, 0, 1, 1, 1, 0, 0, 0, 0),
(19, '2007-01-15 18:15:37', 1, 3, 1, 1, 5555, 15, 5, 0, 0, 0, 1, 13, 190, 85),
(18, '2007-01-15 18:15:37', 1, 3, 1, 1, 5555, 15, 5, 0, 0, 0, 1, 13, 190, 85),
(17, '2007-01-15 18:15:37', 1, 3, 1, 1, 5555, 15, 5, 0, 0, 0, 1, 13, 190, 85),
(22, '2007-01-20 09:01:44', 1, 1, 1, 0, 0, 15, 0, 1, 10, 100, 0, 0, 0, 0);
[/code] -
I've consulted a few books, but I just don't know what to look for. Please, anyone, give me a hint!
-
Hi Everyone,
I'd like to select from the table below the unique memberId with the highest (powerReps * powerWeight) value and the latest date! It's part of a competition system. Every record is a test, connected to a user...
So I'd like to select the latest test of a unique member with the highest (powerReps * powerWeight) value. If that user had better results in the past, I'd like to disregard that, because only his latest test results are valid.
Can anyone help me? Here's the table... I've made an image out of it, I don't know how else to present it.
[url=http://imageshack.us][img]http://img487.imageshack.us/img487/7583/tabletg6.gif[/img][/url]
I've tried a number of queries, including this one, but the results are not what I aimed for... I'd like to order by MAX(powerReps * powerWeight) but I can't seem to get this done... I've tried a number of other queries, but no dice...
[code]
SELECT DISTINCT memberId, MAX(powerReps * powerWeight) as maxPower, date
FROM test
GROUP BY memberId DESC
[/code]
Thanks in advance! -
Off course, how silly of me...
[code]$title = "<h2><a href=\"index.html\">The Index Page</a></h2>";[/code]
Thank you very much!
It worked like a charm!
-
Hello everyone,
How can I turn this:
[code]
$title = "<h2><a href="index.html">The Index Page</a></h2>";
[/code]
Into this:
[code]
$urlFromTitle = "index.html";
[/code]
I just don't understand the PHP manual.
Thanks in advance!
-
Hello everyone,
I'm happy to say that it's working now!
Thank you, haaglin, for your insights.
This is what I made of it!
In the login page, to start the session
[code]if ( $user == 'test' && $pass == 'test' ) {
$_SESSION['ingelogd'] = true;
//I've checked to see if register_globals is enabled: it is!
echo "<h1>Welcome, ".$user.". </h1>";
echo $links;
exit;
} else {
echo "<p>Incorrect login data. Try again.</p>";
}[/code]
In the main page, to check if user logged in:
[code]<?php
if (!$_SESSION['ingelogd']) {
echo "<h1>Failed</h1>";
echo "<a href='login.php'>Please click here to login.</a>";
} else {
echo "<h1>It Worked! This is the main page!</h1>";
}
?>[/code]
On the first line of both documents, to resume the session:
[code]<?php session_start(); ?>[/code]
Thank you all very much.
Good night. -
Thank you!
I will try it! -
Hello,
This is a mind boggler for me...
What am I doing wrong here?
Any help would greatly be appreciated!
I've simplified these two scripts to make testing easy...
The original script checks user input with a db entry.
The main page featured more functionality.
Basics: On submit of user & pass, login.php runs itself. If login & pass = correct, it shows links. Links lead to main page. However, main page keeps saying I'm not logged in, while I started the session up above in the page...
I just keep getting the error message (!$logged_in_user) of the main page...
LOGIN.PHP
[code]
<?php session_start(); ?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Login</title>
</head>
<body>
<?php
$user = stripslashes(trim($_POST["user"]));
$pass = stripslashes(trim($_POST["pass"]));
$links = "<p><a href='main.php'>Main page</a> | <a href='logout.php'>Log out</a></p>";
if ($user && $pass) {
if ($logged_in_user == $user) {
echo "<p>".$user.", you're already logged in.</p>";
echo $links;
exit;
}
//there in the original there is a mysql statement to check $user & $pass with database entries
//I just wanted to make testing easy for you guys
if ( $user == 'test' && $pass == 'test' ) {
$logged_in_user = $user;
//I've checked to see if register_globals is enabled: it is!
session_register("logged_in_user");
echo "<h1>Welcome, ".$logged_in_user.". </h1>";
echo $links;
exit;
} else {
echo "<p>Incorrect login data. Try again.</p>";
}
} else if ($user || $pass) {
echo "<p>Please fill in both fields.</p>";
}
?>
<form id="loginData" name="loginData" method="post" action="login.php">
<h1>Login</h1>
<p>
<label>
<input type="text" name="user" /> Username
</label>
</p>
<p>
<label>
<input type="text" name="pass" /> Password
</label>
</p>
<p>
<input type="submit" name="Submit" value="Verzenden" />
<input type="reset" name="Submit2" value="Wissen" />
</p>
</form>
</body>
</html>
[/code]
MAIN.PHP
[code]<?php session_start(); ?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Main</title>
</head>
<body>
<?php
if (!$logged_in_user) {
echo "<h1>Failed</h1>";
echo "<a href='login.php'>Please click here to login.</a>";
} else {
echo "<h1>It Worked! This is the main page!</h1>";
}
?>
</body>
</html>
[/code]
Please help!
-
No problem. I was going to remove that bit of code, to see what would happen. I'm going to write the content of the blog, using a handmade CMS with the [url=http://"http://tinymce.moxiecode.com/"]tinyMCE [/url]plugin. For those who don't know tinyMCE: it is a free "what you see is what you get"-editor, which converts a text field of a form into a word-like interface which you can use to format text in real life. So, yes I'm going to need the contents of the tables as they are. But thank you for your useful remark.
-
I see. I think I understand the majority of it. I did indeed notice the explanatory links at the end of one of your posts. It's just that I've always found the PHP manual somewhat hard to understand. I think in time, things will go better.
I dealt with the error. Thanks. Everything seems to work fine. I hope other people are helped with this too. -
I updated the script with your new adjustments. Thank you. It's gonna be a while before I can make a script like this. But I've got high hopes (because of good support).
-
Fantastic. It works great! Thank you!
As my signature says, I'm eager to learn new things. I've reviewed your script, and I'd like to understand it a little better. At high level I think I understand it: the script gets an array of tags for each article, then splits the array into variables and then assigns an a href to every tag.
There are some things I don't understand however:
[list]
[*]What does $limit do?
[*]What does the isset do?
[/list]
Thanks again for everything. I don't mind if you don't answer these questions. I will find them out eventually, I guess. The result of the script is temporarily available at the [url=http://chocolata.be/weblog/indextest.php]same link[/url] as my last post. -
I tested out your script just now, but unfortunately, I get an error message. Sorry to trouble you with this, but most of the code you entered I do not yet understand. Thus, I cannot figure out what the problem is.
The following error code is printed out just below the tags. The tags state: no_records.
[code]Warning: Invalid argument supplied for foreach() in /home/chocol/domains/chocolata.be/public_html/weblog/indextest.php on line 91[/code]
If you would like to see the html version of this page, go to http://chocolata.be/weblog/indextest.php. Then you can see first hand where the problem is (if you'd like to do that).
[code]<?php
// the connection data and general strings
include("../php/server.inc.php");
// connection
$db = mysql_connect($dbserver,$dbuser,$dbpass)
or die ($dbconnecterror);
mysql_select_db($dbnaam, $db)
or die ($dbselecterror);
// the query that outputs the title, article and date per article
$query = 'SELECT * FROM articles ';
$limit = TRUE;
/**
* If a tagId has been sent, show only those articles
* with that tagId. Validated by preg_match and casted
* to an (int)
*/
if (isset($_GET['tagid']) && preg_match('/^[0-9]+$/', $_GET['tagid']))
{
$query .= 'INNER JOIN reference AS r ON r.refArtcleId = articles.articleId WHERE r.refTagId = '.(int)$GET['tagid'].' ';
$limit = FALSE;
}
$query .= 'ORDER BY date DESC ';
if ($limit)
{
$query .= 'LIMIT 3 ';
}
$result = mysql_query($query)
or die ($queryerror);
// Kijk of er resultaten zijn
if (mysql_num_rows($result) > 0){
//itereer over records en print elk veld
while($row = mysql_fetch_array($result)){
$title = stripslashes($row["title"]);
$article = stripslashes($row["article"]);
$date = stripslashes($row["date"]);
$articleId = trim($row["articleId"]);
//tags are handled here and made into a list
$tags = getTags($articleId);
$tag_list = 'no_records';
if (count($tags))
{
/**
* Each tag is made into a link with the tagId
* as a GET variable
*/
foreach ($tag_list as $tagId => $tag)
{
$tag_list = "<a href=\"{$_SERVER['articles.php']}?tagid=
{$tagId}\">".htmlentities($tag, ENT_QUOTES)."</a>, ";
}
}
//htmlentities applies to output
echo
"
<h4>".htmlentities($title, ENT_QUOTES)."</h4>
<p>".htmlentities($article, ENT_QUOTES)."</p>
<span class='specs'>Geplaatst op: ".$date."</span><br />
<span class='specs'>Tags:".$tag_list."</span>
"
;
}
} else {
echo "".$norecords."";
}
// free the set of results
mysql_free_result($result);
//close this connection
mysql_close($db);
//function that gets the tags
function getTags($articleId)
{
$query = "SELECT tagId, tag FROM reference, tags WHERE refArticleId = '
".$articleId."' AND tagId = refTagId ORDER BY tag";
$result = mysql_query($query) or die($query."<br />\n".mysql_error());
$tags = array();
if (mysql_num_rows($result) > 0)
{
while($row = mysql_fetch_array($result))
{
$tags[($row['tagId'])] = $row['tag'];
}
}
return $tags;
}
?>[/code] -
Thank you for your thorough attention, I'll make sure to do this.
-
Wow, that's a lot to chew at once. I can't thank you enough for your efforts. I'm going to take my time to try and understand this. I'll get back to you with the results. This sure is harder than I thought it would be :) .
-
I think all I need to know is how to cut the $tag_list in explicit strings or something. That way, I can use these strings within my while loop. Or am I thinking too simplisticly?
-
Thank you. I made the changes.
The tags work nicely now, but I was thinking: how could I make a different link out of every tag? I want to show all articles related to that tag after clicking a certain tag. Is there any way to do this? Could you get me started?
-
Thank you very much! It worked like a charm. I could have never discovered this by myself. Thank you again. See the result at http://www.chocolata.be/weblog/index.php
-
Thanks for your reply. I noticed the missing $. It was a copy-paste error.
I understand now, that I break out of my while loop thanks to the return.
Could you give me a literal example of how I could output to an array?
I've read the php manual, but I can't understand the syntax.
Thanks again. -
Dear everyone,
I'm fairly new to MySQL/PHP and therefore I have the following problem:
I'm making a tagging system for my blog. I have three MySQL tables: article (articleId, title, article, date) , tag (tagId, tag) and reference (refId, refArticleId, refTagId).
As you might expect, I am connecting the article table to the tag table via a third table. This third table (reference) only contains a unique identifyer (refId) and the unique identifyers of the respective two other tables.
Now, everything works fine up to the tagging system. I just let a while loop iterate over my article table, which outputs the title of my article, the article and the date. But then, in the location where I would like to output the different tags that apply to that certain article, things go wrong.
As you can see I call the function getTags in my first while loop, so as to output the tags in connection to a certain article. Within this function I use "return". This causes the script to output only one tag (where usually there are more). If I try to replace the return by an "echo" or "print", I do get the right tags. However, they are placed totally wrong.
What am I doing wrong here? I'd appreciate it if anyone could help me. Thanks in advance.
[hr]
[code]
// the connection data and general strings
include("../php/server.inc.php");
// connection
$db = mysql_connect($dbserver,$dbuser,$dbpass)
or die ($dbconnecterror);
mysql_select_db($dbnaam, $db)
or die ($dbselecterror);
// the query that outputs the title, article and date per article
$query = "SELECT * FROM articles ORDER BY date DESC LIMIT 3";
$result = mysql_query($query)
or die ($queryerror);
// Kijk of er resultaten zijn
if (mysql_num_rows($result) > 0){
// itereer over records en print elk veld
while($row = mysql_fetch_array($result)){
$title = stripslashes($row["title"]);
$article = stripslashes($row["article"]);
$date = stripslashes($row["date"]);
$articleId = trim($row["articleId"]);
echo
"
<h4>".$title."</h4>
<p>".$article."</p>
<span class='specs'>Geplaatst op: ".$date."</span><br />
<span class='specs'>Tags:".getTags(articleId)."</span>
"
;
}
} else {
echo "".$norecords."";
}
//function that gets the tags
function getTags($articleId) {
$query = "SELECT tag FROM reference, tags WHERE refArticleId = '".$articleId."' AND tagId = refTagId ORDER BY tag";
$result = mysql_query($query)
or die($queryerror);
if (mysql_num_rows($result) > 0) {
while($row = mysql_fetch_array($result)){
return $row[tag];
}
} else {
echo $norecords;
}
}
// free the set of results
mysql_free_result($result);
//close this connection
mysql_close($db);
[/code]
MySQL Search Function
in MySQL Help
Posted
Owkay, thank you. I'll figure it out!