Page 1 of 2

[solved]address 2 problems with syntax, full code provided

Posted: Tue Mar 09, 2004 2:59 am
by malcolmboston
ok heres the code in question first and foremost

Code: Select all

if ((!$sortby) || (!$orderby) || (!$limitto))
{
//the query defined 
$query = "SELECT *, DATE_FORMAT(last_login, '%H:%i - %d/%m/%y ') AS last_login FROM members ORDER BY access_level ASC" or die(mysql_error()); 
// query the database 
$result = mysql_query($query); 
// display all the results for everyone
$row1 = mysql_fetch_array($result, MYSQL_ASSOC);
$nomembers = mysql_num_rows($result);
// if statement to change the value of $row1[last_login] in case the user is yet
// to login to the site
/////////////////add here////////////////////////////
print "
<form action="profile-viewer.php" method="post" name="$row1[username]_form" id="$row1[username]_id">
<tr> 
<td class="usernamerow">$row1[username]
<input name="username2pass" type="hidden" id="username2pass" value="$row1[username]">
</td>
<td class="emailrow">$row1[email]</td>
<td class="registeredrow">$row1[register_date]</td>
<td class="activityrow">$row1[last_login]</td>
<td class="usergrouprow">$row1[access_level]</td>
<td class="buttonrow"><input class="adminbutton" name="$row1[username]button" type="submit" id="$row[username]button" value="More.."></td>
</tr>
</form>";
while ($row2 = mysql_fetch_assoc($result))
print "
<form action="profile-viewer.php" method="post" name="$row2[username]_form" id="$row2[username]_id">
    <tr> 
      <td class="usernamerow">$row2[username]
	  <input name="username2pass" type="hidden" id="username2pass" value="$row2[username]">
	  </td>
      <td class="emailrow">$row2[email]</td>
      <td class="registeredrow">$row2[register_date]</td>
      <td class="activityrow">$row2[last_login]</td>
      <td class="usergrouprow">$row2[access_level]</td>
      <td class="buttonrow"><input class="adminbutton" name="$row2[username]button" type="submit" id="$row2[username]button" value="More.."></td>
    </tr>
</form>";
}
do not worry about line 1 ($!sortby etc) as that is for a search function that i have

anyway basically i want to things to happen with this code and no matter how i try to write it it wont work:

1) create a DATE_FORMAT for more than one field
me wrote:

Code: Select all

$query = "SELECT *, DATE_FORMAT(last_login, '%H:%i - %d/%m/%y ') AS last_login FROM members ORDER BY access_level ASC" or die(mysql_error());
i want it to be something like:

Code: Select all

$query = "SELECT *, DATE_FORMAT(last_login, register_date '%H:%i - %d/%m/%y ,'%d/%m/%y') AS last_login, register_date FROM members ORDER BY access_level ASC" or die(mysql_error());
however this wont work and i kind find anything about 'double' formatting in the MySQL manual only single

also

i have a value in my last_login of "NULL" (completely empty in MySQL) so when i print this it comes out with a blank value and knocks my CSS cells out of place because of the lack of &nbsp; or <spacer> so i tried this

Code: Select all

// if statement to change the value of $row1[last_login] in case the user is yet
// to login to the site
if ($row1[last_login] = "NULL")
{
$row1[last_login] = "Never";
}
else
{

}
i dont think this is correct but atm my heads 2 screwed up over constant coding

i also tried

Code: Select all

// if statement to change the value of $row1[last_login] in case the user is yet
// to login to the site
if (empty($row1[last_login]))
{
$row1[last_login] = "Never";
}
else
{

}
and this didnt work

any help would be great

Mal

Posted: Tue Mar 09, 2004 3:22 am
by malcolmboston
anyone? :?

Posted: Tue Mar 09, 2004 3:24 am
by JayBird
not a solution to you problem, but noticing your use of arrays in your second problem, you may want to read this Array do's and don'ts - Why is $foo[bar] wrong?

In reference to $row1[last_login]

Mark

Posted: Tue Mar 09, 2004 3:29 am
by JayBird
as for your SQL query, you can't combine date_format as far as i know, so you will have to do this

Code: Select all

SELECT *, DATE_FORMAT(last_login '%H:%i - %d/%m/%y ,'%d/%m/%y') AS last_login, DATE_FORMAT(register_date '%H:%i - %d/%m/%y ,'%d/%m/%y') AS register_date FROM members ORDER BY access_level ASC" or die(mysql_error());
Mark

Posted: Tue Mar 09, 2004 3:29 am
by malcolmboston
yeah i know that, i just written the script out fast

anyone else?

Posted: Tue Mar 09, 2004 3:30 am
by twigletmac
Bech100 wrote:as for your SQL query, you can't combine date_format as far as i know, so you will have to do this

Code: Select all

SELECT *, DATE_FORMAT(last_login '%H:%i - %d/%m/%y ,'%d/%m/%y') AS last_login, DATE_FORMAT(register_date '%H:%i - %d/%m/%y ,'%d/%m/%y') AS register_date FROM members ORDER BY access_level ASC" or die(mysql_error());
Mark
Did you try that?

Mac

Posted: Tue Mar 09, 2004 3:31 am
by malcolmboston
ah ok, kool thanks buddy

anyone any ideas about the NULL issue?

Posted: Tue Mar 09, 2004 3:32 am
by JayBird
as for your first NULL example, it should have been like this

Code: Select all

// if statement to change the value of $row1[last_login] in case the user is yet 
// to login to the site 
if ($row1['last_login'] == NULL) // No quote on the NULL value
{ 
$row1['last_login'] = "Never"; 
} 
else 
{ 

}
Mark

Posted: Tue Mar 09, 2004 3:32 am
by markl999
If anything if ($row1[last_login] = "NULL") should be using ==

*EDIT* oops, didn't notice i'd been beaten to it ;)

Posted: Tue Mar 09, 2004 3:33 am
by malcolmboston
not yet twig, i havent built up the DB on my 'on-line' web server here to test it out but it looks ok, ill try it out later on and debug it myself if theres an problems

and in regard to the previous poster

to be honest i dont think that is correctly anyway so i never thought about operators but no = should be fine

Posted: Tue Mar 09, 2004 3:41 am
by twigletmac
malcolmboston wrote:to be honest i dont think that is correctly anyway so i never thought about operators but no = should be fine
Not in an if statement ;) , = is an assignment operator, == is a comparison operator - they do very different things.

Mac

Posted: Tue Mar 09, 2004 3:49 am
by malcolmboston
ah :wink: now i see why i had that sort of problem in the past with some scripts

ok so im not sure if it would work but if i did

Code: Select all

if ((!$sortby) || (!$orderby) || (!$limitto)) 
{ 
//the query defined 
$query = "SELECT *, DATE_FORMAT(last_login, '%H:%i - %d/%m/%y ') AS last_login FROM members ORDER BY access_level ASC" or die(mysql_error()); 
// query the database 
$result = mysql_query($query); 
// display all the results for everyone 
$row1 = mysql_fetch_array($result, MYSQL_ASSOC); 
$nomembers = mysql_num_rows($result); 
// if statement to change the value of $row1[last_login] in case the user is yet 
// to login to the site 
/////////////////////////////////new here////////////////////////
if ($row1[last_login]== Null)
{
$row1[last_login] = "Never";
////////////////////// end of new ////////////////////////////
print " 
<form action="profile-viewer.php" method="post" name="$row1[username]_form" id="$row1[username]_id"> 
<tr> 
<td class="usernamerow">$row1[username] 
<input name="username2pass" type="hidden" id="username2pass" value="$row1[username]"> 
</td> 
<td class="emailrow">$row1[email]</td> 
<td class="registeredrow">$row1[register_date]</td> 
<td class="activityrow">$row1[last_login]</td> 
<td class="usergrouprow">$row1[access_level]</td> 
<td class="buttonrow"><input class="adminbutton" name="$row1[username]button" type="submit" id="$row[username]button" value="More.."></td> 
</tr> 
</form>";
//////////////////// new here ///////////////////////////
if ($row2[last_login]== Null)
{
$row2[last_login] = "Never";
/////////////////////////// end of new /////////////////////
while ($row2 = mysql_fetch_assoc($result)) 
print " 
<form action="profile-viewer.php" method="post" name="$row2[username]_form" id="$row2[username]_id"> 
    <tr> 
      <td class="usernamerow">$row2[username] 
     <input name="username2pass" type="hidden" id="username2pass" value="$row2[username]"> 
     </td> 
      <td class="emailrow">$row2[email]</td> 
      <td class="registeredrow">$row2[register_date]</td> 
      <td class="activityrow">$row2[last_login]</td> 
      <td class="usergrouprow">$row2[access_level]</td> 
      <td class="buttonrow"><input class="adminbutton" name="$row2[username]button" type="submit" id="$row2[username]button" value="More.."></td>
then would that only change the values for the NULL fields or as soon as it found its first NULL field turn all of the rest of the fields printed after and treat them all as NULL?

sorry for asking its just i cant test it out atm

Posted: Tue Mar 09, 2004 4:00 am
by JayBird
well, in the code above, you aren't actually looping through the results from what i can see, so you will only be outputting one row.

Mark

Posted: Tue Mar 09, 2004 4:05 am
by malcolmboston
yes bech you are right

however it is looping result and there is currently 325 people in the database some having a NULL value in there last_login column and some not

so as soon as it found its first NULL field turn all of the rest of the fields printed after and treat them all as NULL?

Posted: Tue Mar 09, 2004 4:11 am
by JayBird
malcolmboston wrote:so as soon as it found its first NULL field turn all of the rest of the fields printed after and treat them all as NULL?
Not if you in a loop i.e.

Loop starts

last_login = NULL

last_login = Never

Output results

Loop starts again

last_login = "saturday"

Output results

Loop starts again

last_login = NULL

last_login = "never"

Output results

etc etc etc

Mark