[SOLVED] scripting a job database

rilana · November 16, 2008

Hi this is the error Msg

SELECT * FROM jobs WHERE (region IN (glarus, zurich)) AND (beruf IN (tech, kader))
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /search.php on line 36
Unknown column 'glarus' in 'where clause'

shouldn't it be column region? But the jobs are there with glarus and zurich and tech and kader... I dont get it.

Barand · November 16, 2008

the string values should be in single quotes if insist on using the names and not the IDs

WHERE (region IN ('glarus', 'zurich')) AND (beruf IN ('tech', 'kader'))

rilana · November 17, 2008

Hy, you are right, if I say

SELECT * FROM jobs WHERE (region IN ('glarus', 'zurich')) AND (beruf IN ('tech', 'kader'))

the output works. I acomplished to get an output like SELECT * FROM jobs WHERE (region IN ('glarus, zurich')) AND (beruf IN ('tech,kader')) but the two' before and after , I cant get in there... I tryed so many ways, I evan tryed implode and other things, but the output is always the same. The two editional ' I managed getting in the beginning and end of the string I got by $_POST['beruf']); putting $berufe and $regions in ''. Am I realy that untalented? Or is it hard to figure this out?

Thank you for your help, Rilana

rilana · November 17, 2008

OK, found a solution.... this works join("', '", $_POST['region']); jupiiii thank you!

rilana · November 24, 2008

Hi guyes, can you tell me how I can say something like show some text from the database, but show only the first 100 characters.... What kind of tutorial would I have to search for, to learn how to do this?

Thank you verry much, Rilana

rilana · November 24, 2008

sorry, I found this, and it seams to work...

	echo substr($row['stellenbeschrieb'],0,100);

is this a good solution?

Barand · November 25, 2008

Just look through the string functions for substr

EDIT Didn't see the above reply until I posted - it was at top of next page.

rilana · November 25, 2008

Thank you for your reply, I will look into it! :-)

rilana · November 30, 2008

Hy guyes, I am having a news Problem with this. I guess it's not realy a problem, I just dont know how to do it. I am having a page with listing all the jobs, but only titel, some text and then there should be a link, that links to a page which shows all the details. So I tryed to send the id within the link like this.

echo "<a href=\"stellenDetail.php?id=$row[id]\">  $row[stellenbeschrieb] </a>";

the error is that the querry is empty. What else do I need to say on the detail page so it knows which data to publish??? Thank you for you help. Rilana

Barand · November 30, 2008

what is the code in "stellenDetail.php" that is trying to use the id?

rilana · November 30, 2008

Hy Barand I am glade you are here. I tryed this:

<?

$con = mysql_connect("localhost", "s", "s") or die(mysql_error());
mysql_select_db("s") or die(mysql_error());


$sql = "SELECT * FROM jobs where id = \"$_POST[id]\" ";
echo "$sql";

$result = mysql_query($sql) or die(mysql_error());
$fetchid = mysql_fetch_array($result);

//print table
echo "<table width='100%' border='0' cellpadding='1'>";

$id=($fetchid['id']);
$datumsanzeige=($fetchid['datumsanzeige']);
$datum=(date("d.m.y", strtotime($fetchid["datum"])));
$today = date("d.m.y");

if($datumsanzeige == "fake")
    {
    $datumDef = "$today";
    }
  else
    {
    $datumDef = "$datum";
    }

// Print out the contents of each row into a table
echo "<tr><td>"; 
echo $fetchid['position'];
echo "</td></tr><tr><td>"; 
echo $fetchid['stellenbeschrieb'];
echo "</td></tr><tr><td>"; 
echo "</td></tr>"; 


echo "</table>";
echo mysql_error();

?>

Barand · November 30, 2008

you are sending via a querystring in the link so you need $_GET['id']

$id = intval ($_GET['id']);   // for safety
$sql = "SELECT * FROM jobs where id = $id";

rilana · November 30, 2008

thank you verry much, that worked.

rilana · November 30, 2008

I have another question, and I have no clue if this is evan possible.

    $sql = "SELECT * FROM jobs $whereclause order by datum DESC";

This line orders the listing by datum (date). But since there are job-entrys that are always the same entry but are also always available, I have another database entry which is called datumsanzeige. This is what I did in my job-listings to show the right date.

$datumsanzeige=($row['datumsanzeige']);
$datum=(date("d.m.y", strtotime($row["datum"])));
$today = date("d.m.y");

if($datumsanzeige == "fake")
    {
    $datumDef = "$today";
    }
  else
    {
    $datumDef = "$datum";
    }

But now the problem is the ordering. If I have a fake datumsanzeige it should be listed on the top of the page and not after the entry from the database $datum.

Is there a way to do the ordering with an if statement?

Thanks for your help, I hope you understand what I mean.

Barand · December 1, 2008

you can try

$sql = "SELECT * FROM jobs $whereclause

order by datumsanzeige = 'fake' DESC, datum DESC";

rilana · December 15, 2008

wow thank you, I like your thinking. It worked... thanks a lot.

rilana · December 15, 2008

I am trying to do a pagination and I am just in the beginning, but I am allready running into problems... I did this:

   if  (count($where) > 0) $whereclause = ' WHERE ' . join (' AND ', $where);
    
    $sql = "SELECT * FROM jobs $whereclause order by datumsanzeige = 'fake' DESC, datum DESC"; 
}



$result = mysql_query($sql,$con);
[color=red]$r = mysql_fetch_row($result);
$numrows = $r[0]; 

echo "$numrows";[/color]

But the wierd thing is it gives me a different number then the output of listing the object does. Lets say I have a List with 28 outputs, the number will only say 19 or so... is there some logic to it? something is not right here...

rilana · December 15, 2008

I also realized that the aditional line screws up my output. For example when I say show me all entries from the kader in zurich, it will show me the number 74 but no other output, I know that I have only one entry... so when I take the new lines out, it will show me the correct 1 entry I have... I dont get it.

Barand · December 15, 2008

$numrows = mysql_num_rows ($result);

$r[0] just gives the contents of the first column.

rilana · December 15, 2008

    
    if  (count($where) > 0) $whereclause = ' WHERE ' . join (' AND ', $where);
    
    $sql = "SELECT * FROM jobs $whereclause order by datumsanzeige = 'fake' DESC, datum DESC"; 
$result = mysql_query($sql, $con) or trigger_error("SQL", E_USER_ERROR);
$r = mysql_fetch_row($result);
$numrows = $r[0];

// number of rows to show per page
$rowsperpage = 10;
// find out total pages
$totalpages = ceil($numrows / $rowsperpage);

// get the current page or set a default
if (isset($_GET['currentpage']) && is_numeric($_GET['currentpage'])) {
   	// cast var as int
   	$currentpage = (int) $_GET['currentpage'];
	} else {
   // default page num
   		$currentpage = 1;
} // end if

// if current page is greater than total pages...
if ($currentpage > $totalpages) {
   	// set current page to last page
  		 $currentpage = $totalpages;
} // end if
// if current page is less than first page...
if ($currentpage < 1) {
   		// set current page to first page
  		 $currentpage = 1;
} // end if

// the offset of the list, based on current page 
$offset = ($currentpage - 1) * $rowsperpage;

// get the info from the db 
$sql = "SELECT * FROM jobs $whereclause order by datumsanzeige = 'fake' DESC, datum DESC LIMIT $offset, $rowsperpage";
}



$result = mysql_query($sql,$con);




//print table
echo "<table width='100%' border='0' cellpadding='0'>";

while($row = mysql_fetch_array( $result )) {




$id=($row['id']);
$datumsanzeige=($row['datumsanzeige']);
$datum=(date("d.m.y", strtotime($row["datum"])));
$today = date("d.m.y");

if($datumsanzeige == "fake")
    {
    $datumDef = "$today";
    }
  else
    {
    $datumDef = "$datum";
    }




// Print out the contents of each row into a table
echo "<tr><td class='stellen'>"; 
echo $row['position'];
echo "</td><td>"; 
echo "$datumDef";
echo "</td></tr><tr><td>"; 
echo substr($row['stellenbeschrieb'],0,100);
echo "... ";
echo "<a href=\"stellenDetail.php?id=$row[id]\">|weiter > </a>";

echo "</td></tr>"; 
echo "<tr><td>";
echo "</td></tr>";
echo "<tr><td class='line'><br></td><td></td></tr>"; 




} 

echo "</table>";


// while there are rows to be fetched...
while ($list = mysql_fetch_assoc($result)) {
   // echo data
   echo $list['id'] . " : " . $list['number'] . "<br />";
} // end while


/******  build the pagination links ******/
// range of num links to show
$range = 3;

// if not on page 1, don't show back links
if ($currentpage > 1) {
   // show << link to go back to page 1
   echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=1'><<</a> ";
   // get previous page num
   $prevpage = $currentpage - 1;
   // show < link to go back to 1 page
   echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$prevpage'><</a> ";
} // end if 

// loop to show links to range of pages around current page
for ($x = ($currentpage - $range); $x < (($currentpage + $range) + 1); $x++) {
   // if it's a valid page number...
   if (($x > 0) && ($x <= $totalpages)) {
      // if we're on current page...
      if ($x == $currentpage) {
         // 'highlight' it but don't make a link
         echo " [<b>$x</b>] ";
      // if not current page...
      } else {
         // make it a link
 echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$x'>$x</a> ";
      } // end else
   } // end if 
} // end for

// if not on last page, show forward and last page links	
if ($currentpage != $totalpages) {
   // get next page
   $nextpage = $currentpage + 1;
    // echo forward link for next page 
   echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$nextpage'>></a> ";
   // echo forward link for lastpage
   echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$totalpages'>>></a> ";
} // end if
/****** end build pagination links ******/


echo mysql_error();





?>

rilana · December 15, 2008

this is not how I wanted to post the last message at all...

rilana · December 15, 2008

Hi I tryed to understand the tutorial about the pagination on phpfreaks. But somehow it want work.

I dont get an error message anymore, but I still want work right. It limits the output. But unstead of seeing the link for the next page, I just see a [1]. I am not shure what I am doing at all!!!

    
    if  (count($where) > 0) $whereclause = ' WHERE ' . join (' AND ', $where);
    
    $sql = "SELECT * FROM jobs $whereclause order by datumsanzeige = 'fake' DESC, datum DESC"; 
$result = mysql_query($sql, $con) or trigger_error("SQL", E_USER_ERROR);
$r = mysql_fetch_row($result);
$numrows = $r[0];

$rowsperpage = 10;
$totalpages = ceil($numrows / $rowsperpage);

if (isset($_GET['currentpage']) && is_numeric($_GET['currentpage'])) {
   	$currentpage = (int) $_GET['currentpage'];
	} else {
   		$currentpage = 1;
} 

if ($currentpage > $totalpages) {
  		 $currentpage = $totalpages;
} 
if ($currentpage < 1) {
  		 $currentpage = 1;
} 

$offset = ($currentpage - 1) * $rowsperpage;

$sql = "SELECT * FROM jobs $whereclause order by datumsanzeige = 'fake' DESC, datum DESC LIMIT $offset, $rowsperpage";
}

$result = mysql_query($sql,$con);

echo "<table width='100%' border='0' cellpadding='0'>";
while($row = mysql_fetch_array( $result )) {

echo "<tr><td class='stellen'>"; 
echo $row['position'];
echo "</td><td>"; 
echo "$datumDef";
echo "</td></tr><tr><td>"; 
echo substr($row['stellenbeschrieb'],0,100);
echo "... ";
echo "<a href=\"stellenDetail.php?id=$row[id]\">|weiter > </a>";

} 

echo "</table>";

while ($list = mysql_fetch_assoc($result)) {
   echo $list['id'] . " : " . $list['number'] . "<br />";
}

$range = 3;

if ($currentpage > 1) {
   echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=1'><<</a> ";
   $prevpage = $currentpage - 1;
   echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$prevpage'><</a> ";
} 

for ($x = ($currentpage - $range); $x < (($currentpage + $range) + 1); $x++) {
   if (($x > 0) && ($x <= $totalpages)) {
      if ($x == $currentpage) {
         echo " [<b>$x</b>] ";
      } else {
 echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$x'>$x</a> ";
      }
   } 
}

if ($currentpage != $totalpages) {
   $nextpage = $currentpage + 1;
   echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$nextpage'>></a> ";
   echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$totalpages'>>></a> ";
}

echo mysql_error();

?>

rilana · December 19, 2008

Hy guyes, sorry but I am still trying to get the paging fixed... I cut it down to one error message. The first output of the listing and paging seems to work, but as soon as I klick on 2, or any other page, it says:

Warning: mysql_fetch_row(): supplied argument is not a valid MySQL result resource in stellenResult.php on line 80

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/smartper/public_html/db/stellenResult.php on line 82

So there is a problem on line 80 and 82...

$r = mysql_fetch_row($result);  
$numrows = mysql_num_rows ($result);

both use the result variable, and the result variable uses the sql variable...

$sql = "SELECT * FROM jobs $whereclause order by datumsanzeige = 'fake' DESC, datum DESC";

And I beleve it has something to do with that. If I say for example

"SELECT COUNT (*) FROM jobs $whereclause order by datumsanzeige = 'fake' DESC, datum DESC";

Then it will give me the same error message about line 80 and 82... but it will give the error allready by fetching the rows the first time, not like the other way later on when I try to klick on an other page....

Can anyone give me a hint how I can solve this problem? I would realy apreciate it. Thank you, Rilana

rilana · December 19, 2008

I guess I have to find a way to send "$whereclause" to the next page. Is there a way to do that in a link like this?

 echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$prevpage'><</a> ";

rilana · December 19, 2008

ok, I think I solved it. I just took an if away...

if (isset($_POST['btnSubmit']))
{

I dont seem to need that on btnSubmit.... seems to work...

Sign In

[SOLVED] scripting a job database

Recommended Posts

Link to comment

Share on other sites

Top Posters In This Topic

Popular Days

Top Posters In This Topic

Popular Days

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information