Pulling out hair...

[Bigjohn]

I'm trying to make a function:

function make_page_lable($artist) {
$sql = $sql = 'SELECT *'
. 'FROM artists'
. 'WHERE artist LIKE "%'. $artist .'%"';
$result = mysql_query($sql);
if (!$result) {
print mysql_error() . "Error - discriptor Query Failed";  
}
$content="";
$row .= mysql_query($result);
$content = '<h1 align="center">' . $row['artist'] . '</h1>';
$content .= '<p align="center">'. $row['artistbio'].'</p>';
return $content;
} 

I'm using LIKE because the field in the DB is likely to have upper case letters - and to use the same value throughout the page I strlower($artist).

I've tried all sorts of variations on the punctuation. I'm sure its something simple, but my newbie eyes are missing it... Please help?

John

[willybfriendly]

Line 2:

$sql = $sql =

Should be a single
$sql =

WBF

[coopster]

I believe you are inadvertently concatenating your $row variable, and at the same time you aren't fetching a row, you are trying to execute the query again...

$row = mysql_fetch_assoc($result); 

[Timotheos]

Also...

The way you're building the sql statement will make it look something like

SELECT *FROM artistsWHERE artist LIKE "%coopster%";

which I think would throw an error. Need some spacing.

$sql = 'SELECT * '
. 'FROM artists '
. 'WHERE artist LIKE "%'. $artist .'%"';

Relax Bigjohn... we'd hate to see you lose your hair ;-)

[coopster]

True. Actually, why not get rid of all the concatenation?

$sql = "SELECT * FROM artists WHERE artist LIKE '%$artist'%";

Much cleaner ;)

[Bigjohn]

Ok.. I replaced the SQL statement thus:

function make_page_lable($artist) {
$sql = "SELECT * FROM artists WHERE artist LIKE '%$artist'%";
$result = mysql_query($sql);
if (!$result) {
print mysql_error() . "Error - discriptor Query Failed";
}
$content="";
$row .= mysql_query($result);
$content = '<h1 align="center">' . $row['artist'] . '</h1>';
$content .= '<p align="center">'. $row['artistbio'].'</p>';
return $content;
}

And I get the following error :

You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1Error - discriptor Query Failed

that appears to be a doublequote, by the way...
ARRGH.
I'm having a major love/hate relationship with this PHP/MYSQL stuff.... The pages are SO cool.... and they'll be easier to maintain... but ARRRGH.

John
PS. there went another clump of hair.

[willybfriendly]

LIKE '%$artist'%";

Look at the placement of the single quotes. Should that not be

LIKE '%$artist%'";

WBF

[digitsix]

oh yeah, and why bother even setting that $sql var? its totally un necessary... just do:

$result = mysql_query("select bla bla bla bla where artist like '%$artist%'");

and

print mysql_error() . "Error - discriptor Query Failed";

lol, just a nice 'echo mysql_error();' would be sufficient...

and

$row .= mysql_query($result);

since you are basing this query on a like string, there is a possibility that more than one row will be returned, in which case you will need something like this:

$content = "";
while($row = mysql_fetch_array($result)) {
$content = '<h1 align="center">' . $row['artist'] . '</h1>';
$content .= '<p align="center">'. $row['artistbio'].'</p>';
}

return $content;

// that way you will have all the possible data that was output by your query =)

[Bigjohn]

@digitsix -

Thanks for your patience with me. Sorry, butbelieve it or not, the original query (top post) is modified FROM a tutorial. Thanks for giving me a break - I am a 41 yr old dude learning this stuff all over again. The last large program I wrote was in BASIC! (ok, a little Pascal and Cobol...) Where is my GOTO? (LOL).

Anyway - I am only using LIKE because the DB table I'm querrying will have proper case in the name field - ie. Audubon not audubon.

I thought about using select distinct but I can't figure out exactly what/how that returns the info! The query looks great in myPHPadmin, but I can't seem to make SELECT DISTINCT work in my function.

I'll review your post and make the modifications.

John

[webadept]

Hey John, we all have to learn as we go, no problem there as far as I'm concerend.

In MySQL char, and varchar fields are not case sensitive in queries, as you have figured out and are trying to work around. BLOB fields are by the way, like TEXT.

To do a query on a CHAR or VARCHAR field which returns a case sensitive result, use BINARY.

See these MySQL pages for all abou this handy item

[mysql.com...]

and this one has more information on comparisons

[mysql.com...]

Hope that helps, ... I understand the hair thing, but life isn't so bad being bald ;-)

Glenn Hefley

[Bigjohn]

guys:

I modified the code:

function make_page_lable($artist) {
$sql = "SELECT * FROM artists WHERE artist LIKE '%$artist%'";
$result = mysql_query($sql);
if (!$result) {
print mysql_error() . "Error - discriptor Query Failed"; 
}
$content="";
$row .= mysql_query($result);
echo $row;
echo $result;
$content = '<h1 align="center">' . $row['artist'] . '</h1>';
$content .= '<p align="center">'. $row['artistbio'].'</p>';
return $content;
}

note I put some echo's in there - I was not getting any return info (i.e. the query worked but returned NOTHING)... my echo gives me 'Resource ID #5'.

Whasup wid dat? Should I use "mysql_fetch_array" instead of "mysql_query"?

@webadept: thanks. I've been through those pages and thats how I decided I wanted to use LIKE. The fields in this table are ID (auto increment 'smallint'), artist(tinytext), class(tinytext), and artistbio(mediumtext).

John

[coopster]

Right on, Bigjohn. See msg #3. And actually, digitsix offers a very good point in the last part of msg #8 in that "there is a possibility that more than one row will be returned..."

---

Nice catch, WBF, I had a typo (blush) in my "much cleaner" statement there -- sorry about that Bigjohn.

---

Regarding your "sloppy, buggy code" -- I concur with webadept and I'll be bold enough to take it a step further -- I'm glad it is that way. It shows that you took the time to attempt to build something yourself as opposed to coming to a forum and ask others to do you work for you. Cheers to you, Bigjohn. Nothing to be ashamed of.

...and why bother even setting that $sql var? its totally un necessary.

Careful here! Although it may seem unnecessary since you can build the statement and execute the query in one step, it may not be the best practice. Just because you can do it, doesn't mean you should do it. I'm not trying to start an arguement here, I'm merely offering another way to look at things. It will of course, come down to personal preference, but let me explain my line of reasoning...

First, it will make troubleshooting much easier, especially once your statements get more complex and use more and more variables. See the tips (msg #3) in the PHP Troubleshooting [webmasterworld.com] thread regarding this issue.

Second, and this may seem more advanced now, but it will soon make sense...second, you can use an include file to execute your statements or use the PHP PEAR database packages. If you ever decide to port your code to a different database, all of your query statements (if written in compliance to SQL standards) can be executed without modification to your scripts.
(Example: you write an entire application consisting of many scripts working together. Now, you are asked to allow the application to use Postgresql, Oracle, or any other database besides MySQL. Now what do you do? Now you have to go through all your code and replace every mysql_query function with pg_query, ociexecute, etc. What a nightmare.

In my own, humble opinion, the practice of building your SQL statements in a variable prior to use is a good practice, stick with it.

[edited by: jatar_k at 6:31 pm (utc) on Feb. 25, 2004]

[Bigjohn]

@coopster -
So you're saying that I need to do 'fetch array' instead of mysql_query?

RE: multiple returns... There won't be. Each artist is in the Artists table only once.

so, how do I fix my statement so it returns the artist name and bio?

John

[Netizen]

Another reason for assigning the SQL to a variable is so you can output it via some debugging clause e.g.

$sql="SELECT count(*) FROM mytable WHERE field1='$var'"; 
if ($debugging) { 
 print "My sql statement is $sql<br>\n"; 
} 
mysql_query($sql); 

[Bigjohn]

my fiddling continues:

function make_page_lable($artist) {
$sql = "SELECT * FROM artists WHERE artist LIKE '%$artist%'";
$result = mysql_query($sql);
if (!$result) {
print mysql_error() . "Error - discriptor Query Failed"; 
}
$content="";
$row .= mysql_fetch_assoc($result);
print_r ($row);
$content = '<h1 align="center">' . $row['artist'] . '</h1>';
$content .= '<p align="center">'. $row['artistbio'].'</p>';
return $content;
}

Now I'm just getting the first letter! Whasup? This database stuff can get REALLY confusing.

John

[edited by: jatar_k at 5:08 am (utc) on Feb. 26, 2004]
[edit reason] no personal urls thanks [/edit]

[coopster]

Read msg #3 carefully and you'll notice I stated that you are concatenating [php.net] the variable. You don't want to do that. Notice also the syntax correction I made; I removed the period from in front of the equal sign (this is concatenation). Get that period out of there and you should be golden.

[Bigjohn]

ARRGH!

That was it.

Ok, but WHY? Lots of 'examples' that I've looked at start that way.

and a guy helped me build a big 'variable' for html output with this:

$var .=<<<END
yadda yadda line of stuff here {$othervar} yadda yadda

and it works (it builds the table on the page I linked above). But I tried to do that for a shorter string (I liked the idea of not having to type all the dam ' and \' stuff!) it didn't work... *sigh*.

John

[MedCenter]

you need to redeclare your mysql link resource within your php function.

[Timotheos]

Maybe we could all chip in and buy Bigjohn a toupee.

The .= operator is for strings. It's a great tool when building up strings like you used for your table.

In this case
$row = mysql_fetch_assoc($result);
populates the $row variable as an array set with the first row of the sql results.

Once you've done this then any string in this array is free game.

$row['text'] .= "additional text added on the end";

Clear as mud, right? Just keep coding... it's the best way to learn.

[digitsix]

sorry for beeing so mean in that first post i made that is now deleted... it was like 5am here and i was tired as hell... dunno what i was even doing here... anyway, thats no excuse, keep on truckin, you'll be a pro before you know it ;)

[Bigjohn]

@digitsix - No worries mate. Im just an old fart learning new tricks. I am writing my own code and learning as I go - Notice that I'm using FUNCTIONS for everything? from my old PASCAL days... any of you young pups ever write Pascal? Pl1? PLc? Cobol? That's how long it's been since I've actually PROGRAMMED... Until last week.

@Timotheos

Yes. That makes sense.

So, when 'building' a string, thats the right way (concatenate). When putting a query into a variable, DON'T.

Cool.

So, now I have all my variables, and the page is formatting like it should. Which, of course, is GRAND.

So I move on to my next stage of learning...

I need a function that will:
1 - email a form content to me
2 - update one field in a database
2a - add a record to a database (sales record) with the contents of the form and hidden vars
3 - close the window it's in and return the user to the main menu of the website.

Can this be done? How does one UPDATE a field in a db? I know how to insert a row, but I just want to update one field in a certain row in a certain table.

Should I post this as another thread?

[Timotheos]

Pascal? Ah yes I remember it well... I'm not that far behind you at 36.

I need a function that will:
1 - email a form content to me

mail() [php.net]

2 - update one field in a database
2a - add a record to a database (sales record) with the contents of the form and hidden vars

[webmasterworld.com...] see msg#9

3 - close the window it's in and return the user to the main menu of the website.

header("Location: main.php"); [php.net]

Should I post this as another thread?

Read up, give it your best shot, pull out some more hair and then come back with the specifics (in another post) if you're really stuck.

[jatar_k]

You can keep this one rollin too if you like Bigjohn, no big deal.

I am not so far behind at 31 either ;)

[Bigjohn]

Ok.

SO, i'm going to build an HTML form (I've already programmed the button to pass the values - if you've seen my test site you've seen the buy button)

The button will pass to the form page the table and item number that needs to be updated.

After the user enters the data into the form (is there a way to validate that a person has input an email address >> or any data for that matter into a form?)

On submit, the PHP page that is the form action does an UPDATE to the specified table and an INSERT into the sales log table, and sends me an email with all the data. This page then echos "transaction complete" to the user.

On the subject, how does PHP/SQL keep track of who what and where in this type of scenario?

John
@all -
I don't need a toupe (thanks to NIOXIN!).. but my 'widows peaks' are a hereditary bane...

I only wish you guys were all on webdevelopers forums. this 'bestBBS' software just horribly SUX!

[webadept]

Just took a look at that site you are building, looks like you are doing well. Man, I don't tell anyone I know COBOLT, even under threat of death by ice cream scoop.

Glad you are being taken care of here. Keep coding John, it will all look bright and clear in the next life time.

[Bigjohn]

ok guys - thanks.

Now, am I correct in assuming that ALL the fields from a POST form are available to me on the decendant page using the $_POST() method?

I just put the name of the field into the ()?

John

[jatar_k]

You can loop them to see what you get

foreach ($_POST as $field) {
echo "<p>",$field;
}

You need square brackets like so

$_POST['fieldname'];

[coopster]

Close. It's brackets [], not parenthesis:

$form_variable = (isset($_POST['form_variable']))? $_POST['form_variable'] : '';

This tutorial [php.net] should shed some light, too, Bigjohn.

[Bigjohn]

you guys rock

have a look :

I'm just about finished wiht the mechanincs, then I can tune the CSS.

John

[edited by: jatar_k at 6:41 pm (utc) on Feb. 26, 2004]
[edit reason] sry no personal urls thanks [/edit]

[Bigjohn]

@Timotheos
on that mail() thing...

there is a script on the link you sent along - from nico dated 30 jan 04. Does that look like it will work? This mail thing is more complicated than it sounds. My server runs exim, if that matters, not sendmail.

John