PHP SQL Query Problem

[pspsully]

Hi, im designing a news section for my site. I want the main page to display only the first 200 characters of the article and then i'll have a button to go and read the complete story.

It will work fine and i have nearly all the work done but im having a problem with the 200 characters.

i think i have to use a substring() statement but cant get it to work.

can anyone help me??

Another thing,

i was testing a substring() query i m tring to use but i doest work either.

i have the following code

$res = @mysql_query("SELECT SUBSTRING(name,1,4) FROM employeecontact WHERE name = 'Kelly Lynch'");
print("$res");

But instead of displaying "elly L" it just says "Resource id #11"

i have no idea why!!!!

Thanks again guys

[Calilo]

Your close to the answer, you do need a Substring() statement, i would let the db query intact, giving the whole text in the column. but later on with php i would make that news shorter to 200 chars. like this


substr($news['News'],0,200)


that means, show chars from 0 to 200 and nothing else from the query result $news especifically the "News" column

good luck with that.

Calilo

[pspsully]

I dont quite understand what you mean, im using sql server so it only accepts substring() i think.

i tried to use this piece of code

$res = @mysql_query("SELECT SUBSTRING(user,0,5) FROM users WHERE id = 3");

SUBSTRING($user['user'],0,5);

print("$user");

but i get a fatal error.

I have a database with a table called users, it has 5 colums, id, user, pass, name and email.

say i want to print the first 5 letters of the persons username where there id is equal to 3!!

I dont understand why the above wont work!!!

Thanks again for the help!!

[Calilo]

hi, first, are you using PHP? what db are you using? Mysql ???

if you are using php and MySQL what i meant was:


$res = @mysql_query("SELECT user FROM users WHERE id = 3");

$user = SUBSTRING($user['user'],0,5);
echo $user;


This way, MySQL get the whole string, and the php cuts that sting to only keep the first 200 chars, and then you are printing those 200 chars.

Hope it helps

Calilo

[GJay]

substring() isn't a PHP function, it is (as you first posted...) substr().

to OP:
If you're using MS-SQL, then you need to use the appropriate functions, and not the MySQL ones:
http://uk.php.net/mssql

And your query should be:
SELECT SUBSTRING(name,1,4) AS name FROM employeecontact WHERE name = 'Kelly Lynch'

then it can be accessed with something like:

$rs=mssql_query($query);
$row=mssql_fetch_row($rs);
$name=$row['name'];

[pspsully]

hi, first, are you using PHP? what db are you using? Mysql ???

if you are using php and MySQL what i meant was:


$res = @mysql_query("SELECT user FROM users WHERE id = 3");

$user = SUBSTRING($user['user'],0,5);
echo $user;


This way, MySQL get the whole string, and the php cuts that sting to only keep the first 200 chars, and then you are printing those 200 chars.

Hope it helps

Calilo

That still doesnt work, i get the following message

Fatal error: Call to undefined function: substring()

yes, im using php and sql server.

This is really annoying me now, i've tried everything i can think of but it wont work!!!!!!!!

Thanks for the help Calilo

[goughy000]

you keep saying "sql" server

MYsql or MSsql is what they want to know...


code for PHP's substr


$longsentence = "la la la la i am a very long sentence that would look messy on a page that is short on space and i am just going on and on and on and on";
echo "Short Sentence:" . substr($longsentence, 0, 10) . "...";

[GJay]

Microsoft's SQL database is called 'SQL Server'

[pspsully]

Im using mysql, not mssql, sorry bout the confusion

[Calilo]

Sorry dont know why i typed substring() it should be "substr"

$res = @mysql_query("SELECT user FROM users WHERE id = 3");

$user = substr($user['user'],0,5);
echo $user;

hope is just that

Calilo

[GJay]

Where is the $user array coming from in your code? :|

If you're using MySQL, then replace all the mssql's with mysql in my post above.

[Calilo]

Another way to do it, and probably simpler (what you wanted in the first place) wold be using RIGHT() in de MySQL query (just remeberd this.

$res = @mysql_query("SELECT RIGHT(user,200) FROM users WHERE id = 3");

echo $user['user'];

[EDIT]
Dont know where i got the $user array, probably a mix of post and questions, imagine we created that array like this:
$user = mysql_fetch_array($res)

that seem easier for coding

Calilo

[goughy000]

@GJay

I was thinking it should be


$res = @mysql_query("SELECT user FROM users WHERE id = 3");
$row = mysql_fetch_array($res);
$user = substr($row['user'],0,5);
echo $user;

[dumpfi]

$res = @mysql_query("SELECT SUBSTRING(name,1,4) FROM employeecontact WHERE name = 'Kelly Lynch'");
print("$res");

But instead of displaying "elly L" it just says "Resource id #11"

i have no idea why!!!!If you haven't already figured it out: you must use mysql_fetch_assoc (or row or object) to access the contents of the result set.

dumpfi

[pspsully]

Hey guys, thanks for all the help, i got it to work this way:

$res = @mysql_query("SELECT id,user,name,email FROM users WHERE id = 3");
while( $row = @mysql_fetch_array($res) )
{
$sub = "$row[user]";
echo "" . substr($sub, 0, 5) . "";
}


It was goughy000's $longsentence that cleared it all up for me!!

Thanks alot for everybodys help!!!!!!!!!!!

NOW, on to my next question, lol,

I have sucessfully done the above and my news story appears on my front page like i wanted (the first 200 chars), i am calling the latest news story by using the MAX function in sql,

e.g. my news stories have id's, its an auto increment, so therfore if i call the MAX id i get the latest news story!!!

Thats cool but what if i want to show the top 3 stories on the front of the page but no more than that!!!!!

Thanks again guys!!!!!!

[GJay]

rather than using max, order by id descending, and use 'LIMIT' to just get the first 3 rows

[pspsully]

I knew there ha o be an easier way to do it, i just figured out a way but it is really really long, i was using the MAX value and subtracting 1 from it to get the next story and so on etc.

Anyway, i just tried to use LIMIT but it wont work for me!!!!

Heres the script i tried to use it on:

print("<table id=\"contact_list\" align=\"center\">");
print("<tr>\n");
print("<th scope=\"col\">ID</th>\n");
print("<th scope=\"col\">User</th>\n");
print("<th scope=\"col\">Password</th>\n");
print("<th scope=\"col\">Name</th>\n");
print("<th scope=\"col\">E-Mail
</th>\n");
print("</tr>\n");



$res = @mysql_query("SELECT id,user,pass,name,email FROM users ORDER BY id DESC");
while( $row = @mysql_fetch_array($res) ){

print("<tr><td>$row[id]</td>
<td>$row[user]</td>
<td>$row[pass]</td>
<td>$row[name]</td>

<td>$row[email]</td>\n</tr>\n");

}

This works fine when i use it like this, i get the users in my database, but when i add the LIMIT like this:

$res = @mysql_query("SELECT id,user,pass,name,email FROM users LIMIT '3' ORDER BY id DESC");

or

$res = @mysql_query("SELECT id,user,pass,name,email FROM users ORDER BY id DESC LIMIT '3'");

i get a blank page instead of only 3 users!!

Thanks again for the Help :)

[GJay]

take the quotes off the 3, as it's a number not a string:
$res = @mysql_query("SELECT id,user,pass,name,email FROM users ORDER BY id DESC LIMIT 3");

[pspsully]

Worked like a charm GJAY, i knew i was doing a stupid syntax error, just couldnt figure out where, the example i was looking at had a sting for the limit, a previously declared variable hence the quotation marks.

Thanks for all the help GJAY and everyone else,

Regards,
Sully

[goughy000]

Hey guys, thanks for all the help, i got it to work this way:


$res = @mysql_query("SELECT id,user,name,email FROM users WHERE id = 3");
while( $row = @mysql_fetch_array($res) )
{
$sub = "$row[user]";
echo "" . substr($sub, 0, 5) . "";
}


It was goughy000's $longsentence that cleared it all up for me!!


1) Happy to help

2) you can clean up that echo a bit though...


$res = @mysql_query("SELECT id,user,name,email FROM users WHERE id = 3");
while( $row = @mysql_fetch_array($res) ) {
$sub = "$row[user]";
echo substr($sub, 0, 5);
}


It wont make any change to the output and "if it aint broke, dont fix it" but it'll clean up your code a lil